Please help matching against db

[lingo5]

Hi

I have a form that posts a 10 digit number to create a db query like so (sorry about spanish words but that's my mothertongue:-):

 

<form id="form1" name="form1" method="post" action="../miregalo.php">
<input name="cupon_numero" type="text" class="premioField" id="cupon_numero" maxlength="10" />
<input type="submit" value="DALE !!" />
</form>

 

and this is the query i create to show results for that number:

 

$colname_texts_RS = "-1";
if (isset($_POST['cupon_numero'])) {
 $colname_texts_RS = $_POST['cupon_numero'];
}
mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_texts_RS = sprintf("SELECT * FROM t_cupones WHERE cupon_numero = %s", GetSQLValueString($colname_texts_RS, "text"));
$texts_RS = mysql_query($query_texts_RS, $MySQLconnect) or die(mysql_error());
$row_texts_RS = mysql_fetch_assoc($texts_RS);
$totalRows_texts_RS = mysql_num_rows($texts_RS);

 

this works fine except for one thing....if i enter a coupon numbre that is not on the db i get a syntax error like this:

 

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

 

and hat I would like to be able to do is to get a custom message if the coupo numbre posted by the form does not exist on my db....am I being silly and unable to see a simple solution here?....

[Barand]

use

or die(mysql_error() . "<pre>$query_texts_RS</pre>");

so you can see the submitted query

[lingo5]

thanks barand,

i have now stored the cupon_numero value in a session like so:

 

<?php
session_start();
$_SESSION['cupon_numero'] = $_POST['cupon_numero'];
?>

 

i then get he value passed to the next page to show the result for this query:

 

$colname_texts_RS = "-1";
if (isset($_SESSION['cupon_numero'])) {
 $colname_texts_RS = $_SESSION['cupon_numero'];
}
mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_texts_RS = sprintf("SELECT * FROM t_cupones WHERE cupon_numero = %s", GetSQLValueString($colname_texts_RS, "text"));
$texts_RS = mysql_query($query_texts_RS, $MySQLconnect) or die(mysql_error());
$row_texts_RS = mysql_fetch_assoc($texts_RS);
$totalRows_texts_RS = mysql_num_rows($texts_RS);

 

this works fine when the cupon_numero entered exists on my db...but shows the mentioned syntax error when the entered cupon_numero doesn't exist on the db.

 

I store coupon information on one table (t_cupones) and prize information on another (t_premios).

t_cupones stores the prize_id ad t_premios contains the actual prize.

 

Using the query above I'm able to get the prize id of a given coupon, but what I need is to also query t_premios to get the actual prize description based on the prize_id resulting from the irst query.

 

Sorry...I don't know if this makes sense?

[lingo5]

ok, i have tried this to get the premio_descripcion_esp from the table t_premios based on the id_premio from t_cupones....what am I doing wrong?

 

mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_premios_RS = "SELECT * FROM  t_cupones LEFT JOIN t_premios ON t_cupones.id_premio =  t_premios.id_premio";
$premios_RS = mysql_query($query_premios_RS, $MySQLconnect) or die(mysql_error());
$row_premios_RS = mysql_fetch_assoc($premios_RS);
$totalRows_premios_RS = mysql_num_rows($premios_RS);

[Barand]

echo $row_premios_RS['premio_descripcion_esp'];

[lingo5]

Why do i get "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" when using this query?

 

$colname_cupon_RS = "-1";
if (isset($_POST['cupon_numero'])) {
$colname_cupon_RS = $_POST['cupon_numero'];
}
mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_cupon_RS = sprintf("SELECT * FROM t_cupones WHERE cupon_numero = %s", GetSQLValueString($colname_cupon_RS, "text"));
$cupon_RS = mysql_query($query_cupon_RS, $MySQLconnect) or die(mysql_error());
$row_cupon_RS = mysql_fetch_assoc($cupon_RS);
$totalRows_cupon_RS = mysql_num_rows($cupon_RS;
$id_premio=$row_cupon_RS['id_premio']; // i use this to get id_premio
mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_premio_RS = "SELECT * FROM t_premios WHERE id_premio =$id_premio ";
$premio_RS = mysql_query($query_premio_RS, $MySQLconnect) or die(mysql_error());
$row_premio_RS = mysql_fetch_assoc($premio_RS);
$totalRows_premio_RS = mysql_num_rows($premio_RS);

 

it works fine when the enteredcoupon number exists....but throws the error when the numbre doesn't exist.

please help...i'm reall confused now....thanksss

Edited February 6, 2013 by lingo5

[lingo5]

Barand I've done it !!!!!!!!

 

thanks for your help...this is how I did it

 

$colname_premios_RS = "-1";
if (isset($_POST['cupon_numero'])) {
 $colname_premios_RS = $_POST['cupon_numero'];
}
mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_premios_RS = "SELECT * FROM t_cupones LEFT JOIN t_premios  ON (t_cupones.id_premio = t_premios.id_premio) WHERE cupon_numero = $colname_premios_RS";
$premios_RS = mysql_query($query_premios_RS, $MySQLconnect) or die(mysql_error());
$row_premios_RS = mysql_fetch_assoc($premios_RS);
$totalRows_premios_RS = mysql_num_rows($premios_RS);

[Christian F.]

I've cleaned up you code a bit, and added some comments to explain what the code does.

// CF: First you initialize the coupon variable with -1
$colname_cupon_RS = "-1";

// CF: Then, if the user has submitted a coupon number.
if (isset ($_POST['cupon_numero'])) {
   // CF: You retrieve that data from the $_POST array.
   //        Should have used intval () here, to both validate and protect against SQL injections.
   $colname_cupon_RS = $_POST['cupon_numero'];
}

// CF: Here' we're selecting the correct database to work with.
mysql_select_db($database_MySQLconnect, $MySQLconnect);

// CF: Now we're building the query that should fetch the data from the database.
//        Note that if no number was submitted this will look for coupons with the number -1,
//        which I assume does not exist.
//        Also, the GetSQL... function shouldn't be used here, as we're dealing with an integer.
//        See my comment about intval () above.
$query_cupon_RS = sprintf("SELECT * FROM t_cupones WHERE cupon_numero = %s", GetSQLValueString($colname_cupon_RS, "text"));

// CF: Then we execute the above query, and check for errors with it.
$cupon_RS = mysql_query($query_cupon_RS, $MySQLconnect) or die(mysql_error());

// CF: No errors found, so retrieve the first row. If there is one returned
$row_cupon_RS = mysql_fetch_assoc($cupon_RS);

// CF: Then get the number of rows returned by the above query.
//        Note my previous comment about what it would return if no number was submitted.
$totalRows_cupon_RS = mysql_num_rows($cupon_RS;

// CF: Then, assuming that there was a first row, retrieve the prize ID from it.
//        Again, what happens here if no coupon ID was given?
$id_premio=$row_cupon_RS['id_premio']; // i use this to get id_premio

// CF: Select the database again? Completely unnecessary, delete.
mysql_select_db($database_MySQLconnect, $MySQLconnect);

// CF: Build a query to fetch the prizes, using the ID from the _assumed_ first row of the previous query.
$query_premio_RS = "SELECT * FROM t_premios WHERE id_premio =$id_premio ";

// CF: Run that query, and check for errors. At which point you get your error message.
//        Echo out the last query if you don't see why.
$premio_RS = mysql_query($query_premio_RS, $MySQLconnect) or die(mysql_error());

$row_premio_RS = mysql_fetch_assoc($premio_RS);
$totalRows_premio_RS = mysql_num_rows($premio_RS);

 

It should, hopefully, help explain why you're getting the problems you are.

I also have two recommendations for you, which will help you a lot in the long run:

1. Use JOINs in your queries. As there is no need to run two queries here. A simple JOIN would have fetched all of the information you needed at once, and you wouldn't have had this problem.
   
2. I strongly recommend finding another editor, or at the very least stop using DW to generate the code for you. DW is notoriously bad at this, and produces a lot crappy, slow, insecure and buggy code.

 

Added: LOL, what do you know. Fixed while I was typing.

Oh, well, glad to see that you got it sorted out. Using joins too.

Edited February 6, 2013 by Christian F.

[lingo5]

wow christian thanks for that explanation.

I finally got it working by using JOIN in my query.

Thanks again

Edited February 6, 2013 by lingo5

[Christian F.]

You're welcome, and thanks for sharing your solution so that others might benefit from it too.