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:

Code: Select all

<?php
unset($q);
?>
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 :D.

-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 :D(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"; 
?>