Working with variables on select

edszr · March 7, 2008

HI there,

I'm a complete noobie, so I apologize in advance for any ignorance/stupidity

I have a database with 5 fields:

Name

Phone number

address

city

zip

I want to be able to search using 1,2,3,4 or 5 of the fields above. My question is how do I structure my select statement if I've only filled in name and city, for example? I don't think this will work:

$query="SELECT*FROM contacts WHERE name='$name' phone='$phone' addr='$addr' city='$city' zip='$zip';

If I've only filled in 2 of the 5 fields, correct?

thanks in advance,

Ed

ratcateme · March 7, 2008

you could make some php code like this

<?php
$where=array();
if($name!=''){
$where[]="name = '{$name}'";
}
if($name!=''){
$where[]="name = '{$name}'";
}
if($addr!=''){
$where[]="addr = '{$addr}'";
}
if($city!=''){
$where[]="city = '{$city}'";
}
if($zip!=''){
$where[]="zip = '{$zip}'";
}
$where=implode(' AND ',$where);
$query='SELECT * FROM contacts WHERE '.$where;

i think that should work

Scott.

wildteen88 · March 7, 2008

EDIT: Too slow beaten to it

Build the query dynamically, eg:

if(isset($_POST['submit']))
{
    if(!empty($_POST['name']) && trim($_POST['name']) != '')
    {
        $name = ($_POST['name']);
        $where_clause[] = "name='$name'";
    }

    if(is_numeric($_POST['phone']) && trim($_POST['phone']) != '')
    {
        $phone = $_POST['phone'];
        $where_clause[] = "phone='$phone'";
    }

    if(!empty($_POST['addr']) && trim($_POST['addr']) != '')
    {
        $addr = ($_POST['addr']);
        $where_clause[] = "addr='$addr'";
    }

    if(!empty($_POST['city']) && trim($_POST['city']) != '')
    {
        $city = ($_POST['city']);
        $where_clause[] = "city='$city'";
    }

    if(!empty($_POST['zip']) && trim($_POST['zip']) != '')
    {
        $zip = ($_POST['zip']);
        $where_clause[] = "zip='$zip'";
    }

    if(is_array($where_clause) && count($where_clause) != 0)
    {
        $wc = 'WHERE ' . implode(' AND ', $where_clause);
    }

    $query = 'SELECT * FROM contacts ' . $wc;

    echo '<pre>' . $query . '</pre>';
}

soycharliente · March 7, 2008

That's how I do it. Except for the double name check

Side note, I use

empty()

and not

$var != ''

because empty() checks a lot of different types of values all in one. My $0.02

EDIT: wild beat me to the use of empty.

uniflare · March 7, 2008

Just thought i'd add my example; this way uses "POST" vars from a user submitted form, and stops mysql injection

<?php

// connect to mysql

// Initially set the array
$vars = Array();

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['name'])){ $vars[] =  "`name`='".mysql_escape_string($_POST['name'])."'"; }
if(isset($_POST['phone'])){ $vars[] =  "`phone`='".mysql_escape_string($_POST['phone'])."'"; }
if(isset($_POST['addr'])){ $vars[] =  "`addr`='".mysql_escape_string($_POST['addr'])."'"; }
if(isset($_POST['city'])){ $vars[] =  "`city`='".mysql_escape_string($_POST['city'])."'"; }
if(isset($_POST['zip'])){ $vars[] =  "`zip`='".mysql_escape_string($_POST['zip'])."'"; }

// If user did not submit any queries...
if(!isset($vars[0])){ exit("Please Choose At Least One Filter"); }

// Count how many where queries is requested
if(count($vars) > 1){      // If there is more than 1 where query then 'implode' the array with " and ", (convert to str and insert " and " between each item)
      $where = "WHERE ".implode(" and ",$vars);
}else{      // Otherwise assume there is only 1 where query
      $where = "WHERE ".$vars[0];
}

// Make the Query
$query="SELECT*FROM contacts $where LIMIT 30";

?>

hope this helps,

edszr · March 8, 2008

Thanks all so much for your input! I'm looking forward to trying these examples.

thanks again!

Ed

edszr · March 10, 2008

Thanks again for all of your help. After trying each suggestion, I got the farthest with uniflare's. But I'm not quite there yet...

I start with an html file that looks like this:

<html>
<body>
<pre>
Enter your search criteria in the appropriate field below.

<form method="post" action="junk2a.php">
First Name: <input type="text" name="sfirst"><br>
Last Name: <input type="text" name="slast"><br>
Phone: <input type="text" name="sphone"><br>
Mobile: <input type="text" name="smobile"><br>
Fax: <input type="text" name="sfax"><br>
E-mail: <input type="text" name="semail"><br>
Web: <input type="text" name="sweb"><br>

</form>

</pre>
</body>
</html>

Here's a copy of the php code I'm using. I added some echo statements to uniflare's code to help me get a sense of how far I'm getting before things fail:

<?
//this script outputs the data in table format.

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

$vars = Array();

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'"; }
if(isset($_POST['slast'])){ $vars[] =  "`slast`='".mysql_escape_string($_POST['slast'])."'"; }
if(isset($_POST['sphone'])){ $vars[] =  "`sphone`='".mysql_escape_string($_POST['sphone'])."'"; }
if(isset($_POST['smobile'])){ $vars[] =  "`smobile`='".mysql_escape_string($_POST['smobile'])."'"; }
if(isset($_POST['sfax'])){ $vars[] =  "`sfax`='".mysql_escape_string($_POST['sfax'])."'"; }
if(isset($_POST['semail'])){ $vars[] =  "`semail`='".mysql_escape_string($_POST['semail'])."'"; }
if(isset($_POST['sweb'])){ $vars[] =  "`sweb`='".mysql_escape_string($_POST['sweb'])."'"; }

echo "value of sfirst is $sfirst<br>" ;
echo "value of slast is $slast<br>" ;
echo "value of sphone is $sphone<br>" ;
echo "value of smobile is $smobile<br>" ;
echo "value of sfax is $sfax<br>" ;
echo "value of semail is $semail<br>" ;
echo "value of sweb is $sweb<br>" ;
echo "value of vars is $vars<br>" ;

// If user did not submit any queries...
if(!isset($vars[0])){ exit("Please Choose At Least One Filter"); }

// Count how many where queries is requested
if(count($vars) > 1){      // If there is more than 1 where query then 'implode' the array with " and ", (convert to str and insert " and " between each item)
echo "vars was greater than 1<br>" ;
      $where = "WHERE ".implode(" and ",$vars);
}else{      // Otherwise assume there is only 1 where query
echo "vars was less than 1<br>" ;
      $where = "WHERE ".$vars[0];
}

echo "the value of where is $where<br>" ;
// Make the Query
$query="SELECT*FROM contacts $where LIMIT 30";


$result=mysql_query($query);
echo "result is $result" ;

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>


<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

//echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";
?>


<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $first." ".$last; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $mobile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $fax; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $email; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $web; ?></font></td>
</tr>


<?
$i++;
}

echo "</table>";

?>

When I enter a last name that I know is in the database, this is what I get:

[pre]value of sfirst is

value of slast is roberts

value of sphone is

value of smobile is

value of sfax is

value of semail is

value of sweb is

value of vars is Array

vars was greater than 1

the value of where is WHERE `sfirst`='' and `slast`='roberts' and `sphone`='' and `smobile`='' and `sfax`='' and `semail`='' and `sweb`=''

result is

Database Output

Name Phone Mobile Fax E-mail Website

[/pre]

So the php script shows me the name I entered, but it's not returning the record and it thinks there was more than 1 var entered.

Thanks in advance for your patience!

Ed

MadTechie · March 10, 2008

try changing

if(isset($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'"; }

to

if(isset($_POST['sfirst'])){ $vars[] =  (!empty($_POST['sfirst']))?"`sfirst`='".mysql_escape_string($_POST['sfirst'])."'":""; }

apply same logic to all (note the (!empty($_POST['sfirst']))? at the start and :"" at the end)

also a debug tip

change

echo "value of vars is $vars<br>" ;

to

echo "value of vars is ";
print_r($vars);
echo "<br>" ;

edszr · March 10, 2008

MadTechie,

Thanks for your help and thanks for the tip re: print_r

While my output did change a little, I'm still getting essentially the same result:

<pre>

value of sfirst is

value of slast is roberts

value of sphone is

value of smobile is

value of sfax is

value of semail is

value of sweb is

value of vars is Array ( [0] => [1] => `slast`='roberts' [2] => [3] => [4] => [5] => [6] => )

vars was greater than 1

value of where is WHERE and `slast`='roberts' and and and and and

result is

Database Output

Name Phone Mobile Fax E-mail Website

</pre>

Weird how it thinks there's more than 1 var entered...

Ed

MadTechie · March 10, 2008

ahh i should of read all the code!!

try this

if(isset($_POST['sfirst']) && !empty($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'" }

edszr · March 10, 2008

hey, no worries!

When I use that modified code, I get a blank page, so i guess it's bombing right off the top. Here's that block of code:

if(isset($_POST['sfirst']) && !empty($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'" }
if(isset($_POST['slast']) && !empty($_POST['slast'])){ $vars[] =  "`slast`='".mysql_escape_string($_POST['slast'])."'" }
if(isset($_POST['sphone']) && !empty($_POST['sphone'])){ $vars[] =  "`sphone`='".mysql_escape_string($_POST['sphone'])."'" }
if(isset($_POST['smobile']) && !empty($_POST['smobile'])){ $vars[] =  "`smobile`='".mysql_escape_string($_POST['smobile'])."'" }
if(isset($_POST['sfax']) && !empty($_POST['sfax'])){ $vars[] =  "`sfax`='".mysql_escape_string($_POST['sfax'])."'" }
if(isset($_POST['semail']) && !empty($_POST['semail'])){ $vars[] =  "`semail`='".mysql_escape_string($_POST['semail'])."'" }
if(isset($_POST['sweb']) && !empty($_POST['sweb'])){ $vars[] =  "`sweb`='".mysql_escape_string($_POST['sweb'])."'" }

thanks!

uniflare · March 10, 2008

Try this bud:

<?php
//this script outputs the data in table format.

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

$vars = Array();

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['sfirst']) && $_POST['sfirst'] != null){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'"; }
if(isset($_POST['slast']) && $_POST['slast'] != null){ $vars[] =  "`slast`='".mysql_escape_string($_POST['slast'])."'"; }
if(isset($_POST['sphone']) && $_POST['sphone'] != null){ $vars[] =  "`sphone`='".mysql_escape_string($_POST['sphone'])."'"; }
if(isset($_POST['smobile']) && $_POST['smobile'] != null){ $vars[] =  "`smobile`='".mysql_escape_string($_POST['smobile'])."'"; }
if(isset($_POST['sfax']) && $_POST['sfax'] != null){ $vars[] =  "`sfax`='".mysql_escape_string($_POST['sfax'])."'"; }
if(isset($_POST['semail']) && $_POST['semail'] != null){ $vars[] =  "`semail`='".mysql_escape_string($_POST['semail'])."'"; }
if(isset($_POST['sweb']) && $_POST['sweb'] != null){ $vars[] =  "`sweb`='".mysql_escape_string($_POST['sweb'])."'"; }

// echo "value of sfirst is $sfirst<br>\n" ;
// echo "value of slast is $slast<br>\n" ;
// echo "value of sphone is $sphone<br>\n" ;
// echo "value of smobile is $smobile<br>\n" ;
// echo "value of sfax is $sfax<br>\n" ;
// echo "value of semail is $semail<br>\n" ;
// echo "value of sweb is $sweb<br>\n" ;
// echo "value of vars is $vars<br>\n" ;

// If user did not submit any queries...
if(!isset($vars[0])){ exit("Please Choose At Least One Filter"); }

// Count how many where queries is requested
if(count($vars) > 1){      // If there is more than 1 where query then 'implode' the array with " and ", (convert to str and insert " and " between each item)
//echo "vars was greater than 1<br>\n" ;
      $where = "WHERE ".implode(" and ",$vars);
}else{      // Otherwise assume there is only 1 where query
//echo "vars was less than 1<br>\n" ;
      $where = "WHERE ".$vars[0];
}

//echo "the value of where is $where<br>\n" ;
// Make the Query
$query="SELECT * FROM `contacts` $where LIMIT 30";

$result = mysql_query($query);
echo("result is: "); print_r($result);echo("<br />\n");

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>


<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$first =m ysql_result($result,$i,"first");
$last = mysql_result($result,$i,"last");
$phone = mysql_result($result,$i,"phone");
$mobile = mysql_result($result,$i,"mobile");
$fax = mysql_result($result,$i,"fax");
$email = mysql_result($result,$i,"email");
$web = mysql_result($result,$i,"web");

//echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";
?>


<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $first." ".$last; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $mobile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $fax; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $email; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $web; ?></font></td>
</tr>


<?
$i++;
}

echo "</table>";

?>

Blank page usually means an error somewhere that is not being displayed, eg:

put error_reporting("E_ALL^E_NOTICE"); at the start of your script, if not works then try:

ini_set("error_reporting","E_ALL"); at start

hope this helps,

edszr · March 10, 2008

uniflare....thanks for the suggestions, but I'm still getting a blank page...even with the error reporting statement in there. Do you know if php makes a log file that I can check to see what it may be complaining about?

thanks!

Ed

uniflare · March 10, 2008

check your apache folder for a folder called "logs" and open "error.log".

also, put an exit function at the start of the page like so: exit("test");

then if that works move the function further down the script, if it works repeat until you can find the bad area.

hope this helps,

MadTechie · March 11, 2008

try this


<?php
//this script outputs the data in table format.

mysql_connect("localhost",$user,$password);
mysql_select_db($database) or die( "Unable to select database");

$vars = Array();

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['sfirst']) && !empty($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'"; }
if(isset($_POST['slast']) && !empty($_POST['slast'])){ $vars[] =  "`slast`='".mysql_escape_string($_POST['slast'])."'"; }
if(isset($_POST['sphone']) && !empty($_POST['sphone'])){ $vars[] =  "`sphone`='".mysql_escape_string($_POST['sphone'])."'"; }
if(isset($_POST['smobile']) && !empty($_POST['smobile'])){ $vars[] =  "`smobile`='".mysql_escape_string($_POST['smobile'])."'"; }
if(isset($_POST['sfax']) && !empty($_POST['sfax'])){ $vars[] =  "`sfax`='".mysql_escape_string($_POST['sfax'])."'"; }
if(isset($_POST['semail']) && !empty($_POST['semail'])){ $vars[] =  "`semail`='".mysql_escape_string($_POST['semail'])."'"; }
if(isset($_POST['sweb']) && !empty($_POST['sweb'])){ $vars[] =  "`sweb`='".mysql_escape_string($_POST['sweb'])."'"; }

// echo "value of sfirst is $sfirst<br>\n" ;
// echo "value of slast is $slast<br>\n" ;
// echo "value of sphone is $sphone<br>\n" ;
// echo "value of smobile is $smobile<br>\n" ;
// echo "value of sfax is $sfax<br>\n" ;
// echo "value of semail is $semail<br>\n" ;
// echo "value of sweb is $sweb<br>\n" ;
// echo "value of vars is $vars<br>\n" ;

// If user did not submit any queries...
if(!isset($vars[0])){ exit("Please Choose At Least One Filter"); }

// Count how many where queries is requested
if(count($vars) > 1){      // If there is more than 1 where query then 'implode' the array with " and ", (convert to str and insert " and " between each item)
//echo "vars was greater than 1<br>\n" ;
      $where = "WHERE ".implode(" and ",$vars);
}else{      // Otherwise assume there is only 1 where query
//echo "vars was less than 1<br>\n" ;
      $where = "WHERE ".$vars[0];
}

//echo "the value of where is $where<br>\n" ;
// Make the Query
$query="SELECT * FROM `contacts` $where LIMIT 30";

$result = mysql_query($query);
echo("result is: "); print_r($result);echo("<br />\n");

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>


<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?php
while ($row = mysql_fetch_assoc($result))
{
$first = $row["first"];
$last = $row["last"];
$phone = $row["phone"];
$mobile = $row["mobile"];
$fax = $row["fax"];
$email = $row["email"];
$web = $row["web"];

/*personal dislike of this style of code
$i=0;
while ($i < $num)
{
$first =mysql_result($result,$i,"first");
$last = mysql_result($result,$i,"last");
$phone = mysql_result($result,$i,"phone");
$mobile = mysql_result($result,$i,"mobile");
$fax = mysql_result($result,$i,"fax");
$email = mysql_result($result,$i,"email");
$web = mysql_result($result,$i,"web");*/

//echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";
?>


<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $first." ".$last; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $mobile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $fax; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $email; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $web; ?></font></td>
</tr>


<?php
//$i++; //noted needed
} // close while

mysql_free_result($result);

echo "</table>";

?>

edszr · March 17, 2008

Sorry I took so long getting back...Been swamped with stuff at work....

Thanks again for all of your patience and help. Looking at the error log, I was able to determine that I had a space in my code where there shouldn't be one:

$first =m ysql_result($result,$i,"first");

I got that fixed up, and using MadT's latest suggestion of code, I'm still not getting anything returned from my database:

[pre]

result is:

Database Output

Name Phone Mobile Fax E-mail Website

[/pre]

I'm searching on the last name of "roberts" ... I just double checked my database and I have 2 records in there with the last name of "roberts"

thanks!

Ed

MadTechie · March 18, 2008

need more info..

try this code with the debug code (it should help)


<?php
//this script outputs the data in table format.

mysql_connect("localhost",$user,$password);
mysql_select_db($database) or die( "Unable to select database");

$vars = Array();

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['sfirst']) && !empty($_POST['sfirst'])){ $vars[] =  "`sfirst`='".mysql_escape_string($_POST['sfirst'])."'"; }
if(isset($_POST['slast']) && !empty($_POST['slast'])){ $vars[] =  "`slast`='".mysql_escape_string($_POST['slast'])."'"; }
if(isset($_POST['sphone']) && !empty($_POST['sphone'])){ $vars[] =  "`sphone`='".mysql_escape_string($_POST['sphone'])."'"; }
if(isset($_POST['smobile']) && !empty($_POST['smobile'])){ $vars[] =  "`smobile`='".mysql_escape_string($_POST['smobile'])."'"; }
if(isset($_POST['sfax']) && !empty($_POST['sfax'])){ $vars[] =  "`sfax`='".mysql_escape_string($_POST['sfax'])."'"; }
if(isset($_POST['semail']) && !empty($_POST['semail'])){ $vars[] =  "`semail`='".mysql_escape_string($_POST['semail'])."'"; }
if(isset($_POST['sweb']) && !empty($_POST['sweb'])){ $vars[] =  "`sweb`='".mysql_escape_string($_POST['sweb'])."'"; }

// echo "value of sfirst is $sfirst<br>\n" ;
// echo "value of slast is $slast<br>\n" ;
// echo "value of sphone is $sphone<br>\n" ;
// echo "value of smobile is $smobile<br>\n" ;
// echo "value of sfax is $sfax<br>\n" ;
// echo "value of semail is $semail<br>\n" ;
// echo "value of sweb is $sweb<br>\n" ;
// echo "value of vars is $vars<br>\n" ;

// If user did not submit any queries...
if(!isset($vars[0])){ exit("Please Choose At Least One Filter"); }

// Count how many where queries is requested
if(count($vars) > 1){      // If there is more than 1 where query then 'implode' the array with " and ", (convert to str and insert " and " between each item)
//echo "vars was greater than 1<br>\n" ;
      $where = "WHERE ".implode(" and ",$vars);
}else{      // Otherwise assume there is only 1 where query
//echo "vars was less than 1<br>\n" ;
      $where = "WHERE ".$vars[0];
}

//echo "the value of where is $where<br>\n" ;
// Make the Query
$query="SELECT * FROM `contacts` $where LIMIT 30";

//added debug code
echo "<pre>";
echo $where;
echo "<br>";
print_r($vars);
echo "<br>";
$result = mysql_query($query) or die("SQL='$query' <br> ".mysql_error());
//end debug code
echo("result is: "); print_r($result);echo("<br />\n");

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>


<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
</tr>

<?php
while ($row = mysql_fetch_assoc($result))
{
$first = $row["first"];
$last = $row["last"];
$phone = $row["phone"];
$mobile = $row["mobile"];
$fax = $row["fax"];
$email = $row["email"];
$web = $row["web"];

/*personal dislike of this style of code
$i=0;
while ($i < $num)
{
$first =mysql_result($result,$i,"first");
$last = mysql_result($result,$i,"last");
$phone = mysql_result($result,$i,"phone");
$mobile = mysql_result($result,$i,"mobile");
$fax = mysql_result($result,$i,"fax");
$email = mysql_result($result,$i,"email");
$web = mysql_result($result,$i,"web");*/

//echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";
?>


<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $first." ".$last; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $phone; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $mobile; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $fax; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $email; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $web; ?></font></td>
</tr>


<?php
//$i++; //noted needed
} // close while

mysql_free_result($result);

echo "</table>";

?>

uniflare · March 18, 2008

if yuou have no errors but your resulting page is not what is intended you need to exit certain variables to check they are what ehy are meant to be, eg:

add an echo($query); after setting the $query variable to mke sure the query is what you expected... if it is then try it in phpmyadmin or a test script and fiddle with it until it works, then change the code accordingly.

this is a general debbugging tip to make sure the variables are being set and used correctly...

edszr · March 18, 2008

MadT,

Here's what I get using your debug code:

[pre]

WHERE `slast`='roberts'

Array

(

[0] => `slast`='roberts'

)

SQL='SELECT * FROM `contacts` WHERE `slast`='roberts' LIMIT 30'

Unknown column 'slast' in 'where clause'

[/pre]

"slast" is the variable I use in my search.html file, but in my database the variable is called "last" Am I correct in thinking that the where clause is getting confused because it's looking in my database for the "slast" field...which is not there?

Thanks,

Ed

uniflare · March 18, 2008

you are exactly corect, try this instead

<?php

// Append to the array, the variable 'where' query, if the variable exists.
if(isset($_POST['sfirst']) && !empty($_POST['sfirst'])){ $vars[] =  "`first`='".mysql_escape_string($_POST['sfirst'])."'"; }
if(isset($_POST['slast']) && !empty($_POST['slast'])){ $vars[] =  "`last`='".mysql_escape_string($_POST['slast'])."'"; }
if(isset($_POST['sphone']) && !empty($_POST['sphone'])){ $vars[] =  "`phone`='".mysql_escape_string($_POST['sphone'])."'"; }
if(isset($_POST['smobile']) && !empty($_POST['smobile'])){ $vars[] =  "`mobile`='".mysql_escape_string($_POST['smobile'])."'"; }
if(isset($_POST['sfax']) && !empty($_POST['sfax'])){ $vars[] =  "`fax`='".mysql_escape_string($_POST['sfax'])."'"; }
if(isset($_POST['semail']) && !empty($_POST['semail'])){ $vars[] =  "`email`='".mysql_escape_string($_POST['semail'])."'"; }
if(isset($_POST['sweb']) && !empty($_POST['sweb'])){ $vars[] =  "`web`='".mysql_escape_string($_POST['sweb'])."'"; }

?>

if you notice i changed the strings only.

edszr · March 18, 2008

It's working now!! MadT & Uniflare....you're both the man!

thanks so much for both of your help on this...I learned a lot in this thread.

Ed

Sign In

Working with variables on select

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information