sql and php problem
I am trying to create a mysql query that varies, depending on which input boxes the user has filled in. If they have entered data then that field will be queried with any combination of others, if it is left empty then the query will not include that field.
The below code shows what I have so far..
<?php
if (!isset($_POST['submit']))
{
echo '
<form action="" method="POST">
first name: <input type="text" name="fields[first_name]" value=""><br>
last name: <input type="text" name="fields[last_name]" value=""><br>
email: <input type="text" name="fields[email]" value=""><br>
<input type="submit" name="submit" value="submit">
</form>
';
}
else
{
$sql_array = array();
foreach ($_POST['fields'] as $key => $value)
{
$value = trim($value);
if ($value) {$sql_array[] = $key . " = '" . $value . "'";}
}
if ($sql_array)
{
$sql = 'SELECT * FROM table WHERE ' . implode(', ', $sql_array);
echo $sql;
}
else
{
echo 'all fields blank';
}
}
?> This worked, but I could not display the data with what I normally use:
while($row = mysql_fetch_array( $sql )) {'
Can anyone tell me why this doesn't work and how I can display the query?
Thanks in advance.
timjpriebe posted this at 12:55 — 14th March 2007.
He has: 2,667 posts
Joined: Dec 2004
You haven't actually executed the query. You've just put it into a string is all. For the second block of code, you'll need to do something more like this:
$result = mysql_query($sql);
while($row = mysql_fetch_array( $result )) {
Tim
http://www.tandswebdesign.com
andy206uk posted this at 18:56 — 14th March 2007.
He has: 1,758 posts
Joined: Jul 2002
personally, I prefer to check if their is a result first, before trying to run through it:
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
//****whatever
}
}
Andy
timjpriebe posted this at 19:50 — 14th March 2007.
He has: 2,667 posts
Joined: Dec 2004
Doesn't the while loop mean that if there's zero results, the loop will get execute zero times?
andy206uk posted this at 20:23 — 14th March 2007.
He has: 1,758 posts
Joined: Jul 2002
True, but I believe mysql_fetch_array moans if the result is broken due to invalid SQL, whereas mysql_num_rows just returns '0'. At least, I think so... I was advised to do it this way by a senior developer I know when we were working together on a project - it may just be that I've inherited a bad habit!
Andy
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.