PHP - Mysql Query Not Working

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.
(Table)
qc_reports

(Fields)
id
report_date
report_lot_number    
report_po
report_supplier    
report_buyer    
report_inspectedby    
report_pulptemprange    
report_carrierconditions
report_supplierclaim    
report_carrierclaim    
report_temprecorder    
report_temprange_N    
report_temprange_M    
report_temprange_B    
report_suppliercontact
report_contactedby    
report_time    
report_comments

(Table)
qc_lots

(Fields)
id
report_id
lot_temprange    
lot_commodity    
lot_rpcs    
lot_brand    
lot_terms    
lot_cases    
lot_orgn    
lot_estnum
lot_avgnum

This is the query that I'm trying to do.

Code: [Select]
<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

$sql = substr($sql, 0, -4);

$query = mysql_query($sql);


$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td>&nbsp;</td></tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query))
{
$id = stripslashes($row['id']);
$report_lot_number = stripslashes($row['report_lot_number']);
$report_po = stripslashes($row['report_po']);
$report_date = stripslashes($row['report_date']);

echo "<tr>
<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
</tr>";
}
echo '</table><br><br><br><hr><br><br>';

}

?>
All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

I'm stuck and need some guidance. Can someone help please?

Hey guys,

New to the forum and a newer user of PHP / MySQL.  I am having trouble with some code I've written up.  I don't seem to get any errors when running it, but it's not updating my database the way that it should.  hopefully a simple fix.  I am thinking that it must be on the MySQL side of things. 

Couple of things to start. 

My html form is comprised completely of drop down list inputs.  I'm the only user so I thought this would be the easiest approach.

Because of that I've made my PHP as follows:

Code: [Select]
<?php

$season = $_POST['season'];
$month = $_POST['month'];
$day = $_POST['day'];
$year = $_POST['year'];
$time = $_POST['time'];
$event = $_POST['event'];
$game = $_POST['game'];
$buyin = $_POST['buyin'];

$connect = mysql_connect('localhost','root','') or die('can not connect');

if ($connect)
{
    echo "connected to database";
}

$db = mysql_select_db('dpl') or die('can not find database');

if ($db)
{
    echo "DPL Selected";
}

$query = sprintf("INSERT INTO events (season , month , day , year , time , event , game , buyin)
    VALUES ('%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s' , '%s')",
       $season ,
       $month ,
       $day ,
       $year ,
       $time ,
       $event ,
       $game ,
       $buyin
       );

if ($query)
{
    echo "Your event has been added";
}

?>
My connection is working, my database is selected and I'm even now getting confirmation that my query is working, but when i go to check my database there are no entries in it?

any thoughts?

I've tried the drop down variables as both VARCHAR and TEXT inputs in MySQL, but I can't seem to get it to work.

Any help is greatly appreciated.

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

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 '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate.

What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop.

It doesn't matter if I get to the next page using a header redirect or a form submit.  I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. 

I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. 

Any ideas?

When I echo the POST, it echoes the correct value.
The MySQL portion seems to just ignore it all together.
I've tried changing the dropdown option to just a text field, same thing occurred.
I have text fields right above this particular one that update just fine with the SAME exactly scripting.
if POST, update query, done. Works.

This one for some reason will not.

MySQL portion:
if ($_POST['bUpdate']){
mysql_query("UPDATE `Patients` SET `b` = '$_POST[bUpdate]' WHERE `id` = '".$_GET['id']."'");
}
echo $_POST['bUpdate'];

Form Portion:
Code: [Select]
    <tr onmouseover="color(this, '#baecff');" onmouseout="uncolor(this);">
    <td width="310" colspan="2" align="center"><span class="fontoptions">Postcard Status </span><br />
<?
if ($data['b'] == 1){
echo '<select name="bUpdate"><option value="1" selected>Yes</option><option value="0">No</option></select>';
} else {
echo '<select name="bUpdate"><option value="1">Yes</option><option value="0" selected>No</option></select>';
}

?>
</td>
    </tr>

Can someone review this piece of code and advise what's missing that's preventing the SQL query from working?
Issue: no results being echoed out from $results.

Note: I confirmed the db connection  established (only the query not working - no results pulled from db and displayed via echo statements).



$db = mysql_connect($host,$user,$pw)
        or die("Cannot connect to MySQL.");

mysql_select_db($database,$db)
        or die("Cannot connect to database.");

echo "Success!  Connected to database ".$database."<br /><br />";

// $jsearch is variable sent in from form "title" name
$jsearch = $_POST['jobsearch'];

// I confirmed that all the field names are correct

  //-query  the database table
  $sql="SELECT id,title,details,status FROM jobs WHERE title = '$jsearch';";
  //-run  the query against the mysql query function
  $result=mysql_query($sql) or die("<br>Query string: $result<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
          $title  =$row['title'];
  echo title."<br />";
          $details=$row['details'];
  echo $details."<br />";
  $status=$row['status'];
  echo $status."<br />";
          $ID=$row['id'];

Evening everybody, hoping someone can see my error here!
$sql="INSERT INTO transactions (t_ref, m_affid, m_name, order_val, order_date, order_status, u_id, u_comm) VALUES('$t_ref', '$m_affid', '$m_name', '$order_val', '$order_date', '$order_status', '$u_id', '$u_comm')";
if (!mysql_query($sql))
   { 
    mysql_error();
   }
else

{
$newq = mysql_query("SELECT * FROM userbalance WHERE u_id = '$u_id'");
echo mysql_error();
$row = mysql_fetch_assoc($newq);
$cpending = $row['pending'];
$npending = $cpending + $u_comm;
$updatepending = "INSERT into userbalance (pending)
VALUES ('$npending')
WHERE u_id = '$u_id'";

if (!mysql_query($updatepending))
   { 
    mysql_error();
   }

}

Basically  the 1st query works and inserts the data correctly, however once it gets to $newq = mysql_query("SELECT * FROM userbalance WHERE u_id = '$u_id'"); this query does not insert anything, no errors.

I am using a query to update the db when a user logs in. It sets every value correctly but it doesnt set the colum u_online which is INT(1).


$link->query("UPDATE ".TBL_PREFIX."users
                                SET 
                                u_last_login = '".$config['time_now']."',
                                u_online = 1,
                                u_ip = '".$_SERVER['REMOTE_ADDR']."',
                                u_activity_time = '".$config['time_now']."',
                                u_points = u_points + '$login_points'
                                WHERE u_username = '$username'")
                                or die(print_link_error());


when i echo the query:


UPDATE asf_users 
SET u_last_login = '1300310822',
 u_online = 1, 
u_ip = '127.0.0.1', 
u_activity_time = '1300310822', 
u_points = u_points + '1' 
WHERE u_username = 'doddsey65'


which works because i tried it in phpmyadmin and it changed the column. But it doesnt work in the actual script. It changes every other value that its supposed to but the u_online.

Anyone have any ideas why?

ok Im building a search page.  The user can enter a name, county, category, address into my search field.  The results page will show the details based on that.  It works great so here is a snippet of the working code.




$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.*FROM Clubs

INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
 ((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) "

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"));


This code works great no matter what I enter.  the problem Im having is I only want to show results based on the logged in users county.  I have a variable stored under $user['county']  which is the ID found in the County table under countyID

I have tried writing the code so many different ways.  Here is my latest attemped but it doesnt work.  It breaks the whole query and nothing is displayed.



$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}

$County= "-1";
if (isset($user['county'])) {
  $County= $user['county'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.* FROM Clubs
INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
 ((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) AND County.countyID = %s"

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text")
String($County. "%", "int"));


Thanks

Danny

Im using the following query to pull all topics that have a new reply since the users last visit and where the user has posted within them.

Code: [Select]
$topic_query = $link->query("SELECT t.*, u.*, p.*
            FROM ".TBL_PREFIX."topics as t
            LEFT JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = t.t_poster)
            LEFT JOIN ".TBL_PREFIX."posts as p
            ON (t.t_tid = p.p_tid)
            WHERE t.t_last_post_time > u.u_activity_time
            AND p.p_poster = '".$user->user_name."'
            GROUP BY t.t_tid
            ORDER BY t.t_sticky DESC, t.t_time_posted DESC
            LIMIT $start_page, $topics_to_show")
            or die(print_link_error());

for some reason though it still shows two topics that have a last post time which is less than that of the users activity time.

The last_post_time for the topic is

1300309160

compare that with the users last activity time

1300784679

as you can see the last activity time is more than the last post time. there are no records in the database that are more than the last activity time so i shouldnt be getting any results.

Anyone have any ideas?

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?

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)

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.

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]."'";