Problem with SELECT statement

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Problem with SELECT statement

Post by shadow8807 »

Code: Select all

$transaction_id = "SELECT id FROM payments WHERE lastname='$lastname' AND firstname='$firstname'";

Code: Select all

Error: 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 'cenedese' AND firstname='chris'','ES-PC1')' at line 2
Can anyone see anything wrong with this?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Before you put the dynamic values from PHP, my suggestion is to use the hard coded values in SQL query to know if it works or not.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

It almost looks like you are passing a last name with an apostrophe in it (like O'Malley) and not running it through mysql_real_escape_string().
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Oh yeah, Everah reminded me of mysql_real_escape_string. But using addslashes($string) may be another option. But mysql_real_escape_string() is sql_injection safe.
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Post by shadow8807 »

So what would that line of code look like? Im sorry, im not the greatest with Php/mysql. Still Learning.
The Bat
Forum Newbie
Posts: 14
Joined: Thu Feb 01, 2007 3:57 pm

Post by The Bat »

shadow8807 wrote:So what would that line of code look like? Im sorry, im not the greatest with Php/mysql. Still Learning.
Did you click the link that Everah provided? Just pass your variables through the function, like so:

Code: Select all

$firstname = mysql_real_escape_string($firstname);
$lastname = mysql_real_escape_string($lastname);
And put that before your query.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Printing out the query will usually show you what's wrong too.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Post by shadow8807 »

Code: Select all

$transaction_id = "SELECT id FROM payments WHERE lastname='$lastname' 
AND firstname='$firstname'";
is returning, when echo'd

Code: Select all

SELECT id FROM payments WHERE lastname='' AND firstname=''
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Post the code that assigns the values to $lastname and $firstname.
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Post by shadow8807 »

Assigning $lastname and $firstname

Code: Select all

$lastname = $_SESSION['lastname'];
$firstname = $_SESSION['firstname'];
Finding the id from payments table...

Code: Select all

$result = mysql_query("SELECT id FROM payments WHERE lastname='$lastname' AND firstname='$firstname'");
$row = mysql_fetch_row($result);
$transaction_id = $row["id"];
I echo'd the lastname and firstname and it works perfectly. But the transaction_id comes up blank still.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

SELECT id FROM payments WHERE lastname='' AND firstname=''
Suggests that last name and first name are either not being set properly, are being overridden somewhere, or are losing their value between the assignment and the reference.

For this bit, I would do something like:

Code: Select all

<?php
$sql = "SELECT `id` FROM `payments` WHERE `lastname` = '$lastname' AND `firstname` = '$firstname'";
if (!$result = mysql_query($sql))
{
  die('There was a problem in the query: <b>' . $sql . '</b>: ' . mysql_error());
}

if (mysql_num_rows($result) > 0)
{
  $row = mysql_fetch_row($result);
  $transaction_id = $row["id"];
}
else
{
  echo 'There were no results returned from the query: ' . $sql;
}
?>
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Post by shadow8807 »

Ok wonderful issue happening. Something has to be wrong with the following code, yet I cannot seem to find a problem.

Code: Select all

$result = mysql_query("SELECT id FROM payments WHERE lastname='$lastname' AND firstname='$firstname'");
$row = mysql_fetch_row($result);
$transaction_id = $row["id"];
That is the code used to get the id.

If i were to run through MySQL in the SQL box:

Code: Select all

SELECT id FROM payments 
    WHERE lastname='%ANY LASTNAME existing in table%' AND firstname='%ANY FIRSTNAME matching with last name%'
I get the ID column and ID number

Thats basically what it looks like:
--
ID
1
--

This has got me quite confused. Because the lastname and firstname are set correctly and are not overridden because the echo statement follows right after the transaction_id echo statement. This would leave me to believe that the code that is being used to generate the transaction_id is incorrect or there is a very small error.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Replace this:

Code: Select all

$result = mysql_query("SELECT id FROM payments WHERE lastname='$lastname' AND firstname='$firstname'");
$row = mysql_fetch_row($result);
$transaction_id = $row["id"];
With this and post back what comes out. NOTE: This will halt the script but should throw some useful information...

Code: Select all

<?php
$sql = "SELECT id FROM payments WHERE lastname='$lastname' AND firstname='$firstname'";
echo '<pre>'; var_dump($sql); echo '</pre>';
if (!$result = mysql_query($sql))
{
  die('Could not execute SQL ' . $sql . ' because: ' . mysql_error());
}

if (mysql_num_rows($result))
{
  $row = mysql_fetch_row($result);
  echo '<pre>'; var_dump($row); echo '</pre>';
}
else
{
  echo 'There were no rows returned...';
}
exit;
//$transaction_id = $row["id"];
?>
shadow8807
Forum Newbie
Posts: 9
Joined: Mon Feb 12, 2007 4:00 pm

Post by shadow8807 »

Did as requested.
Output is as follows:
string(71) "SELECT id FROM payments WHERE lastname='Cenedese' AND firstname='Julia'"

array(1) {
[0]=>
string(1) "1"
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Change this:

Code: Select all

$row = mysql_fetch_row($result);
to

Code: Select all

$row = mysql_fetch_array($result);
fetch_row pulls the row by column numbers. Fetch array grabs the row as an array of data. make the change, run it again and post back.
Post Reply