PHP - Query Not Quite Working

I have a basic query where I am retrieving project records. I basically want to show the records from newest to oldest, but I also want to show the Featured projects first. Each Featured project is marked by "1" in the mysql table column.  If it's not featured, then it's marked "0".

$find_records = $db->prepare("SELECT * FROM projects ORDER BY project_id DESC, featured DESC LIMIT 10");
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    
  }
}

The issue with above query is that the ORDER BY works only for the first component(project_id) and ignores the second component(featured). Do you see what's wrong with it?

Hello all, I am using the Lynda.com PHP course to further learn php.  On one of the examples, they are using a SQL Query that works for them but is erroring out for me.  It is the query below for the $page_set variable, particularly the use of the WHERE subject_id = {$subject["id"]}". 


Code: [Select]
<?php require_once ('includes/conf.php'); ?>
<?php include ('includes/header.php'); ?>
<?php require_once ('includes/functions.php'); ?>

<table id="stucture">
<tr>
<td id="navigation">
<ul class="subjects">
<?php 
$subject_set = mysql_query("SELECT * FROM subjects", $connection);
if (!$subject_set) {
die ("Database query failed: " . mysql_error());
}

while ($subject = mysql_fetch_array($subject_set)) {
echo "<li>{$subject["menu_name"]}</li>";
}

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}");  // This appears to be the problem area, unless it is fooling me. It works with this exact statement in their code.
if (!$page_set) {
die ("Database query failed: " . mysql_error());
}
echo "<ul class='pages'>";
while ($page = mysql_fetch_array($page_set)) {
echo "<li>{$page['menu_name']}</li>";
}
echo "</ul>";
?>
</ul>
</td>
<td id="page"><h2>Content Area</h2>
</td>
</tr>
</table>
<?php require ('includes/footer.php'); ?>

The error listed is this:     Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


Thanks,  you all are the best!   BtD

I'm having a problem with adding icon/avatar to my database..this is a submit form where I just submit the avatar url. I don't know how to change the submit link from avatar_add.php?mode=submit to index.php?mode=submit&x=avatar_add.
http://www.graphics.allmerk-noplay.com/index.php?x=avatar_add


Code: [Select]
<?php
$host =""; // host name
$user =""; // user
$pass =""; // password
$dbase =""; // database name

// connect to the server
mysql_connect("$host","$user","$pass");
// and select the database
mysql_select_db($dbase);

$mode=$_GET["mode"]; // get the mode
$QUERY_STRING = 'x=avatar_add';

if(!isset($mode) || empty($mode)) // if mode is empty, switch to case index
{
$mode='index';
}

switch($mode)
{

case 'index'; // displays the form
?>


<form method="post" action="avatar_add.php?mode=submit">
Avatar URL:<br>
<input type="text" name="avatar_url" size="30"><br><br>
<input type="submit" value="Submit">


<?php
break;

case 'submit'; // form processing mode

$avatar_url=$_POST["avatar_url"];

// check for empty fields
if(empty($avatar_url))

{
echo "Error! Please go back and fill in all the required fields!";
// if you have a footer include, insert it before the die statement
die; // stops the script from executing the rest of the code
}

// let's get the date in YYYY-MM-DD format
$date = date("Y-m-d");

$query="INSERT into icons_db (id,avatar_url) VALUES ('','$avatar_url')";
$result=mysql_query($query) or die ("Couldnot execute query: $query." . mysql_error());

if($result) // if mysql query successful
{ echo "Thank you! The avatar has been added to or database."; }

break;

}

?>

Hi there,

I have this query, which outputs the records properly in PhpMyAdmin.
But when using this query inside my PHP code, the result is incomplete.
Instead of retrieving the expected 15 results, I only get the first one twice.
Also, I don't get any errors.

Code: (php) [Select]
<?php
$get_files = "SELECT `id` FROM `files` WHERE `category` = 'W';";

include_once('connection.inc.php');
$con = mysql_connect(MYSQL_SERVER, MYSQL_USERNAME, MYSQL_PASSWORD) or die ("Connection failed: " . mysql_error());
$dbname = "myDB";
$select_db = mysql_select_db($dbname, $con) or die ("Can't open database: " . mysql_error());

$result_files = mysql_query($get_files) or die ("Query failed: " . mysql_error());
$files = mysql_fetch_array($result_files);
$W_files = "'".implode("', '", $files)."'";

// echo $W_files; outputs the first number twice, instead of the expected 15 different ones

$get_checklist = "SELECT * FROM `checklist` WHERE `id` IN ($W_files) ORDER BY `id` ASC;";
$result_checklist = mysql_query($get_checklist) or die ("Query failed: " . mysql_error());

// .... code for displaying
?>

I've got to be missing something pretty basic here.. considering the query is pretty basic.

I'm trying to figure out how to pull a query as an array so I can compare it against another array (array_diff)

I'm doing a mysql_fetch_array, and I'm getting an error ( mysql_fetch_array(): supplied argument is not a valid MySQL result resource):
Quote

$checker = "SELECT ID FROM edible_uses";

$result2 = mysql_fetch_array($checker) or die(mysql_error());
//echoing to see if I'm getting what I need.
echo $row['ID'];


I've done a mysql_query and I get results. The table name and all that is correct. I'm stumped. This seems like a pretty simple query? I tried mysql_fetch_assoc as well. Same result? I tried it with an extra set of parenthesis around it. nope.

my SQL Query wont execute on on following lines:

Code: [Select]
$result = mysql_query("INSERT INTO 'gallery' ('image', 'memberid', 'caption') VALUES ('$newFileName', '$member_id', '$caption')")
    or die (mysql_error());
i get the following error:

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''gallery' ('image', 'memberid', 'caption') VALUES ('gallery/9074849_1.jpg', '1',' at line 1
here is my full code:
Code: [Select]
<?php
require_once('connect.php');

$rand = mt_rand(1,9999999);
$rand2 = mt_rand(1,9999999);
$member_id = $_SESSION['SESS_MEMBER_ID'];
$caption = $_POST["caption"];

if(isset($_FILES['uploaded']['name']))
{
$allowed_filetypes = array('.jpg','.gif','.bmp','.png','.jpeg');
$max_filesize = 524288; // Maximum filesize in BYTES (currently 0.5MB) 
    $fileName = basename($_FILES['uploaded']['name']);
$errors = array();
$target = "gallery/";
$fileBaseName = substr($fileName, 0, strripos($fileName, '.'));

   // Get the extension from the filename.
   $ext = substr($fileName, strpos($fileName,'.'), strlen($fileName)-1); 
 
   //$newFileName = md5($fileBaseName) . $ext;
   $newFileName = $target . $rand . "_" . $member_id.$ext;
   
   // Check if filename already exists
   if(file_exists("gallery/" . $newFileName)) 
    {
     $errors[] = "The file you attempted to upload already exists, please try again.";
    }
   // Check if the filetype is allowed.
   if(!in_array($ext,$allowed_filetypes))
    {
         $errors[] = "The file you attempted to upload is not allowed.";
    }
    
// Now check the filesize.
   if(!filesize($_FILES['uploaded']['tmp_name']) > $max_filesize)
    {
         $errors[] = "The file you attempted to upload is too large.";
    }
  
    // Check if we can upload to the specified path.
   if(!is_writable($target))
    {
         $errors[] = "You cannot upload to the specified directory, please CHMOD it to 777.";
    }
 
    //Here we check that no validation errors have occured.
    if(count($errors)==0)
    {

//Try to upload it.
        if(!move_uploaded_file($_FILES['uploaded']['tmp_name'], $newFileName))
        {
            $errors[] = "Sorry, there was a problem uploading your file.";
        }
    }
    //Lets INSERT database information here
    //Here we check that no validation errors have occured.
    if(count($errors)==0)
    {
$result = mysql_query("INSERT INTO 'gallery' ('image', 'memberid', 'caption') VALUES ('$newFileName', '$member_id', '$caption')")
    or die (mysql_error()); 

{
            $errors[] = "SQL Error.";
        }
}
    //If no errors show confirmation message
    if(count($errors)==0)
    {
         echo "<div class='notification success png_bg'>
<a href='#' class='close'><img src='img/cross_grey_small.png' title='Close this notification' alt='close' /></a>
<div>
The file {$newFileName} has been uploaded<br>\n
</div>
</div>";


//echo "The file {$fileName} has been uploaded";
echo "<br>\n";
echo "<a href='gallery.php'>Go Back</a>\n";

   }
    else
    {
        //show error message
        echo "<div class='notification attention png_bg'>
<a href='#' class='close'><img src='img/cross_grey_small.png' title='Close this notification' alt='close' /></a>
<div>
Sorry your file was not uploaded due to the following errors:<br>\n
</div>
</div>";
//echo "Sorry your file was not uploaded due to the following errors:<br>\n";
        echo "<ul>\n";
        foreach($errors as $error)
        {
            echo "<li>{$error}</li>\n";
        }
        echo "</ul>\n";
echo "<br>\n";
echo "<a href='gallery.php'>Go Back</a>\n";
    }
    
}
else
{
    //Show the form
echo "Use the following form below to add a new image to your gallery;<br />\n";
    echo "<form enctype='multipart/form-data' action='' method='POST'>\n";
    echo "Please choose a file: <input name='uploaded' type='file' /><br />\n";
echo "Caption: <input name='caption' type='text' /><br />\n";
    echo "<input type='submit' value='Upload' />\n";
    echo "</form>\n";

//Echo Tests!
    echo "<br /><br />Random FileName: "; 
echo $rand;
echo "<br />";
echo "member ID: #";
echo $member_id;
}
?>
any help appreciated. its prob something simple.

my table has the following fields:

"gallery"
id (primary Key, AUTO_INC)
memberid (fetched from session)
image (will store image name including extension)
caption (from "caption" text field in form)

Hello I have a general php insert function that I want to use to insert data into tables regulary, the problem is, each time I use it it creates a new row in the table instead of using certain columns to add information to, here is the function, can you tell me why does it insert a new row each time I use it?

class Query{
function add($table,$cols,$vals){
		include 'conx.php';
		$query = "
			INSERT INTO $table($cols) VALUES('$vals');
		";
		
		$res = mysqli_query($con,$query) ;
		
		if($res){
			echo "Inserted value";
		}else{
			echo "Did not insert value";
		}
	}
}

The following mysql query is not returning rows like I expect it to. '$update_field' is a variable,  matching an actual field name in table 'users'. 'user_task[1]' is an integer value. What am I missing here?
Code: [Select]
$query_update_user = "UPDATE users SET ".$update_field." = 'Y' WHERE user_no = '".$user_task[1]."'";

Hi, i am currently designing a new website and am testing a few different compnents of it. One part gathers two variables of the url using the GET method and selects the relevant data from the database. It then performs an UPDATE of a field called balance before INSERTING a new record. Everything works and gets inserted except for the UPDATE.

Here is the code

Code: [Select]
<?php
include ("connect.php");

$id = "";
$offerid = "";

$id = $_GET['user_id'];
$offerid = $_GET['offer_id'];

$sql_user = mysql_query("SELECT * FROM user_info WHERE id = $id");

$sql_offer = mysql_query("SELECT * FROM offers WHERE offerid = $offerid");

$balance += $payout;

mysql_query("UPDATE user_info SET balance = $balance WHERE id = $id");

mysql_query ("INSERT INTO offer_credited (id, offerid, date_credited, payout_to_user) VALUES ('$id', '$offerid', CURDATE(), '$payout')") or die(mysql_error());

echo "Your Offer has been recorded and Updated"
?>
The balance field is a float that i want to increase by the amount of the payout variable which is from the offers table. However, the balance never gets updated and remains at 0.

Any ideas what it could be, i am thinking it is something to do with,

Code: [Select]
$balance += $payout;
But i can't think of what else to do.

Hopefully this isn't too confusing.

Thanks for the help

Basically I need to input data into two tables.

I am running 3 different query's but only 2 of them work. The other one doesn't.

None working query:
mysql_query("INSERT INTO users(username, password, email, pin, key) VALUES('$username', '$password', '$email', '$key', '$pin')");

Working querys:

mysql_query("DELETE FROM beta_keys WHERE keys_new='$key'");

**and**

mysql_query("INSERT INTO beta_keys(keys_used) VALUES('$key')");

So any ideas why the top one doesn't work but the bottom two do?

I would like to edit a record that I already have in my database but my query is not working for some reason. Hope someone can help.

The errors i get are
Notice: Undefined variable: first_name in C:\chemicaluser\www\editinfo.php on line 40
Notice: Undefined variable: last_name in C:\chemicaluser\www\editinfo.php on line 40
Notice: Undefined variable: log_number in C:\chemicaluser\www\editinfo.php on line 40

 .... line 40 = my query
$query = "UPDATE table1 SET first_name = '$first_name', last_name = '$last_name' WHERE log_number = '$log_number' ";

Very simple database, 1 table w/ 2 fields
table name = table1
fields = first_name, last_name

my php file
Code: [Select]
<?php
  require ('/menu.php');
  require_once('sqlconnect.php');
 
  if (isset($_GET['log_number']) && isset($_GET['first_name']) && isset($_GET['last_name']) ) {
    $log_number = $_GET['log_number'];
    $first_name = $_GET['first_name'];
    $last_name = $_GET['last_name'];
  }   
  else if
     (isset($_POST['log_number']) && isset($_POST['first_name']) && isset($_POST['last_name']) ) {
  $log_number = $_POST['log_number'];
  $first_name = $_POST['first_name'];
  $last_name = $_POST['last_name'];
  }

if (isset($_POST['submit'])) {    
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = "UPDATE table1 SET first_name = '$first_name', last_name = '$last_name' WHERE log_number = '$log_number' ";
$result = mysqli_query($dbc, $query)
or die ('Error querying database');
mysqli_close($dbc);
}

?>

my form
Code: [Select]
<form  method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
  <label for="first_name">First name</label>
      <input type="text" id="first_name" name="first_name" value="<?php if (!empty($first_name)) echo $first_name; ?>"/>
  <label for="last_name">Last Name</label>
     <input type="text" id="last_name" name="last_name" value="<?php if (!empty($last_name)) echo $last_name; ?>" />
     <input type="submit" value="Edit Information" name="submit" />
</form>

This is for a faux stock photo website. When I only use the $query string to search for $searchSubject, everything goes golden. Once I put everything else in there though, the search function will not work at all.

Any help will be greatly appreciated. I've been staring at this for too long and it's starting to give me a headache.

PS. This is my first post here. Seems like a great community!

<?php

$searchSubject = $_POST['searchSubject'];
$searchPhotoname = $_POST['searchPhotoname'];
$searchLocation = $_POST['searchLocation'];

if (isset($_POST['search'])) {
   
   //Select records from the database

$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%' OR `name` LIKE '%" . $searchPhotoname . "%' OR `location` LIKE '%" . $searchLocation . "%' ";






} else {
   
   $query = "SELECT * FROM photos";
   
}

$result = mysql_query($query);
// count the number of rows in the database
$num = mysql_num_rows($result);

?>



If I leave it at this:


$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%'";

Then everything works perfect.

Hiya peeps!

I have this code;


$q3 = "SELECT `data` FROM `firm_text_strings` WHERE `id` = '100' AND `languages_id` = 'EN'";
$q4 = "SELECT `data_2` FROM `firm_text_strings` WHERE `id` = '100' AND `languages_id` = 'EN'";

$r3 = mysql_query($q3);
$r4 = mysql_query($q4);
if($r3 && $r4) {
while($w3 = mysql_fetch_assoc($r3) && $w4 = mysql_fetch_assoc($r4)) {
$resultStr = $w3['data'] . '<br />' . $w4['data_2'];
}
} else {
$resultStr = 'error';
}


everything is working up to the while function, it just wont do the loop for some reason. Anyone have any ideas.

Many thanks,

James.

$db->query( "UPDATE user_resources, user_buildings, user_units_nonbattle SET user_resources.uGold=user_resources.uGold+(user_buildings.uTownCenterLevel*(user_units_nonbattle.uMiner*10))" );

When the cron runs, it updates everyone the same regardless. Any idea what i'm doing wrong?

Hi, I'm using $clid = mysqli_real_escape_string ($dbcon, $_POST['clid']); but have come across an issue with certain characters, for example: "bekæ" actually searches for "bekæ", hence no records. The records cannot be changed in the table, so is there a way to fix this?