Page 1 of 1
Query Error
Posted: Sat Oct 04, 2003 6:04 am
by Nay
I've been trying this for about an hour now and I can't figure it out. It's supposed to be a search customers thinggy. I might have missed some stupid character or whatever lol. Help!
Code: Select all
<?
include "includes/header.php";
include "includes/template.php";
include "includes/dates.php";
include "includes/sql.php";
$submit = $_GET['submit'];
if($submit=="yes") {
$name = $_POST['name'];
$domain = $_POST['url'];
$acc_no = $_POST['acc_no'];
$q;
if(!isSet($name) && !isSet($domain) && !isSet($acc_no)) {
echo "You can't do a blank search!";
}
elseif(isSet($name) && !isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM 'customers' WHERE f_name = $name OR m_name = $name OR l_name = $name";
}
elseif(!isset($name) && isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM 'customers' WHERE domain = $domain";
}
elseif(!isSet($name) && !isset($domain) && isSet($acc_no)) {
$q = "SELECT * FROM 'customers' WHERE acc_no = $acc_no";
}
else {
$q = "SELECT * FROM 'customers' WHERE f_name = $name OR m_name = $name OR l_name = $name AND domain = $domain AND acc_no = $acc_no";
}
$result = mysql_query($q, $connection) or die(mysql_error());
}
else {
new_table_title("Search Form");
$print = <<< PRINT
<form name="search" action="search.php?submit=yes" method="post">
Account Number: <input type="text" name="acc_no" size="30" /><br />
Name: <input type="text" name="name" /><br />
Domain name: <input type="text" name="url" size="30" /><br />
<input type="submit" value="Search" />
</form>
PRINT;
new_table_content($print);
}
include "includes/footer.php";
?>
I get:
You have an error near........whatever query ($q) was excuted.
-Nay
Posted: Sat Oct 04, 2003 6:47 am
by JAM
Try this, and you'll see your problem.
Code: Select all
<?php
$q; // bad
//$q = ""; is better
$q = "foo";
echo $q;
?>
Posted: Sat Oct 04, 2003 6:57 am
by Cruzado_Mainfrm
you can also use:
you only have to do this if you have register_globals enabled...
Posted: Tue Oct 07, 2003 7:51 am
by Nay
I added the $q = "foo" and now the code is:
Code: Select all
<?
include "includes/header.php";
include "includes/template.php";
include "includes/dates.php";
include "includes/sql.php";
$submit = $_GET['submit'];
if($submit=="yes") {
$name = $_POST['name'];
$domain = $_POST['url'];
$acc_no = $_POST['acc_no'];
$q = "foo";
if(!isSet($name) && !isSet($domain) && !isSet($acc_no)) {
echo "You can't do a blank search!";
}
elseif(isSet($name) && !isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE f_name = $name OR m_name = $name OR l_name = $name";
}
elseif(!isset($name) && isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE domain = $domain";
}
elseif(!isSet($name) && !isset($domain) && isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE acc_no = $acc_no";
}
else {
$q = "SELECT * FROM customers WHERE f_name = $name OR m_name = $name OR l_name = $name AND domain = $domain AND acc_no = $acc_no";
}
$result = mysql_query($q, $connection) or die(mysql_error());
}
else {
new_table_title("Search Form");
$print = <<< PRINT
<form name="search" action="search.php?submit=yes" method="post">
Account Number: <input type="text" name="acc_no" size="30" /><br />
Name: <input type="text" name="name" /><br />
Domain name: <input type="text" name="url" size="30" /><br />
<input type="submit" value="Search" />
</form>
PRINT;
new_table_content($print);
}
include "includes/footer.php";
?>
and now 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 'AND acc_no =' at line 1
I'm sure it's correct to use multiple "AND"(s) - since it's not my English teahcer's English

.
-Nay
Posted: Tue Oct 07, 2003 8:08 am
by twigletmac
You should print out $q to see what the query looks like (i.e. make sure variables are being passed correctly, the correct quotes have been added around non-numeric values (hint, hint):
Code: Select all
$result = mysql_query($q, $connection) or die(mysql_error().'<p>'.$q.'</p>');
Mac
Posted: Thu Oct 09, 2003 10:40 am
by JAM
No replies, so I'm giving out more free hints...
Code: Select all
$value = "foo bar";
// test one...
$result = mysql_query("select * from x where field = $value")
// test two...
$result = mysql_query("select * from x where field = '$value'")
Q: What happens when $value contains a space in the above queries? (Just one example)
Posted: Thu Oct 09, 2003 9:58 pm
by fractalvibes
Rule Number 1 - if some sequel is throwing up an error, in this case, $q,
always, always, always add something like
echo "<br>" . $q . "<br>" to examine exactly what you are passing the DB server. Most common errror is a simple typo, follow by quoting/not quoting values. Refer to your table structure. If ok - then check for blank value and back track from there.
fv
Posted: Fri Oct 10, 2003 5:36 am
by mathewvp
$q = "SELECT * FROM 'customers' WHERE f_name = $name OR m_name = $name OR l_name = $name";
I think the error is you have quotes for table name customers.Remove the quotes and see
Posted: Fri Oct 10, 2003 4:12 pm
by Cruzado_Mainfrm
1st. use <?php, cuz <? is having compatibility problems with xml

(opt.)
2nd. the real thing: your query doesn't have single quotes for the values...
Code: Select all
<?php
include "includes/header.php";
include "includes/template.php";
include "includes/dates.php";
include "includes/sql.php";
$submit = $_GET['submit'];
if($submit=="yes") {
$name = $_POST['name'];
$domain = $_POST['url'];
$acc_no = $_POST['acc_no'];
$q = "foo"; /* WILL GIVE ERROR IF IS USED AS A QUERY, maybe try some query that will return 0 records always, like: */
//$q = "SELECT * FROM customers WHERE f_name = '-1'";
if(!isSet($name) && !isSet($domain) && !isSet($acc_no)) {
echo "You can't do a blank search!";
}
elseif(isSet($name) && !isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE f_name = '$name' OR m_name = '$name' OR l_name = '$name'";
}
elseif(!isset($name) && isSet($domain) && !isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE domain = '$domain'";
}
elseif(!isSet($name) && !isset($domain) && isSet($acc_no)) {
$q = "SELECT * FROM customers WHERE acc_no = '$acc_no'";
}
else {
$q = "SELECT * FROM customers WHERE f_name = '$name' OR m_name = '$name' OR l_name = '$name' AND domain = '$domain' AND acc_no = '$acc_no'";
}
$result = mysql_query($q, $connection) or die(mysql_error());
}
else {
new_table_title("Search Form");
$print = <<< PRINT
<form name="search" action="search.php?submit=yes" method="post">
Account Number: <input type="text" name="acc_no" size="30" /><br />
Name: <input type="text" name="name" /><br />
Domain name: <input type="text" name="url" size="30" /><br />
<input type="submit" value="Search" />
</form>
PRINT;
new_table_content($print);
}
include "includes/footer.php";
?>