Page 1 of 1

Problem with SELECT statement

Posted: Mon Feb 12, 2007 11:39 pm
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?

Posted: Tue Feb 13, 2007 12:11 am
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.

Posted: Tue Feb 13, 2007 12:56 am
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().

Posted: Tue Feb 13, 2007 1:06 am
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.

Posted: Tue Feb 13, 2007 1:10 am
by shadow8807
So what would that line of code look like? Im sorry, im not the greatest with Php/mysql. Still Learning.

Posted: Tue Feb 13, 2007 1:22 am
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.

Posted: Tue Feb 13, 2007 10:03 am
by pickle
Printing out the query will usually show you what's wrong too.

Posted: Tue Feb 13, 2007 1:09 pm
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=''

Posted: Tue Feb 13, 2007 1:17 pm
by RobertGonzalez
Post the code that assigns the values to $lastname and $firstname.

Posted: Tue Feb 13, 2007 1:45 pm
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.

Posted: Tue Feb 13, 2007 3:10 pm
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;
}
?>

Posted: Tue Feb 13, 2007 3:43 pm
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.

Posted: Tue Feb 13, 2007 3:50 pm
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"];
?>

Posted: Tue Feb 13, 2007 4:03 pm
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"
}

Posted: Tue Feb 13, 2007 5:33 pm
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.