By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,929 Members | 634 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,929 IT Pros & Developers. It's quick & easy.

Problem passing values from a multi-field search page to the MySQL statement

P: 40
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect the fields chosen.

The first page, where they select the search fields and submit:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $db = mysql_connect("localhost", "root", "yeahright");
  3. if (!$db)
  4.    {
  5.    die('Could not connect:'.mysql_error);
  6.    }
  7. mysql_select_db("mytest",$db);
  8. $sql = "SELECT distinct fac_id FROM incidents ORDER BY fac_id";
  9. $result = mysql_query($sql);
  10. if (!$result)
  11.     {
  12.     die('Error: '.mysql_error());
  13.     }
  14. $sql2 = "SELECT distinct person_type FROM incidents ORDER BY person_type";
  15. $result2 = mysql_query($sql2);
  16. if (!$result2)
  17.     {
  18.     die('Error: '.mysql_error());
  19.     }
  20. $sql3 = "SELECT distinct severity FROM incidents ORDER BY severity";
  21. $result3 = mysql_query($sql3);
  22. if (!$result3)
  23.     {
  24.     die('Error: '.mysql_error());
  25.     }
  26. mysql_close($db);
  27. ?>
  28.  
and

Expand|Select|Wrap|Line Numbers
  1. <FORM METHOD="POST" ACTION="ir_multiquery.php">
  2. <p>Search existing Incident Reports by selecting one of the search fields below:</p>
  3. <select name="fac_id" size="1">
  4. <option selected value="">Facility</option>
  5.  
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. while ($myrow = mysql_fetch_array($result))
  3.    {
  4.    echo "<option value='fac_id'>".$myrow["fac_id"]."</option>\n";
  5.    }
  6. ?>
  7.  
Expand|Select|Wrap|Line Numbers
  1. </select>
  2. <select name="person_type" size="1">
  3. <option selected value="">Person Type</option>
  4.  
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. while ($myrow2 = mysql_fetch_array($result2))
  3.    {
  4.    echo "<option value='person_type'>".$myrow2["person_type"]."</option>\n";
  5.    }
  6. ?>
  7.  
Expand|Select|Wrap|Line Numbers
  1. </select>
  2. <select name="severity" size="1">
  3. <option selected value="">Severity Level</option>
  4.  
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. while ($myrow3 = mysql_fetch_array($result3))
  3.    {
  4.    echo "<option value='severity'>".$myrow3["severity"]."</option>\n";
  5.    }
  6. ?>
  7.  
Expand|Select|Wrap|Line Numbers
  1. </select>
  2. <p>
  3. <input type="submit" value="Search" />
  4. </p>
  5. </FORM>
  6.  
ir_multiquery.php:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $db = mysql_connect("localhost", "root", "yeahright");
  3. if (!$db)
  4.    {
  5.    die('Could not connect:'.mysql_error);
  6.    }
  7. mysql_query("mytest", $db);
  8. // The basic SELECT statement
  9. $select = 'SELECT fac_id';
  10. $from = ' FROM incidents';
  11. $where = ' WHERE 1=1';
  12. // If facility was selected
  13. $facility = $_REQUEST["fac_id"];
  14. if ($facility != '') {
  15. echo "REQUEST Facility: ";
  16. echo $_REQUEST["fac_id"];
  17. echo " ";
  18. $where .= ' AND fac_id='.$_REQUEST["fac_id"].'';
  19. //$where .= " AND fac_id='000955'";
  20. }
  21. // If person type was selected
  22. $persontype = $_REQUEST["person_type"];
  23. if ($persontype != '') {
  24. echo "Persontype: ";
  25. echo $_REQUEST["person_type"];
  26. $where .= ' AND person_type='.$_REQUEST["person_type"].'';
  27. }
  28. // If severity was selected
  29. $severity = $_REQUEST["severity"];
  30. if ($severity != '') {
  31. echo "Severity: ";
  32. $_REQUEST["severity"];
  33. $where .= ' AND severity='.$_REQUEST["severity"].'';
  34. }
  35. echo " Final Select: ";
  36. echo $select;
  37. echo $from;
  38. echo $where;
  39. ?>
  40.  
and

Expand|Select|Wrap|Line Numbers
  1. <table>
  2. <tr><th>Query</th><th>Results</th></tr>
  3.  
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $query  = sprintf($select . $from . $where);
  3. echo 'query: ';
  4. echo $query;
  5. if (!$query)
  6.    {
  7.    die('Could not run query:'.mysql_error);
  8.    }
  9. $result = mysql_query($query);
  10. echo '<p>result: ';
  11. echo $result;
  12. echo '</p>';
  13. if (!$result) {
  14. echo '</table>';
  15. exit('<p>Error retrieving data from db - this is from the $result var<br />'.
  16. 'Error: ' . mysql_error() . '</p>');
  17. }
  18. else
  19. {
  20. echo '<p>Eureka!</p>';
  21. }
  22. while ($myrow = mysql_fetch_array($result))
  23. {
  24. echo "<tr valign='top'>\n";
  25. $facility = $myrow['fac_id'];
  26. echo "<td>$facility</td>\n";
  27. echo "</tr>\n";
  28. }
  29. mysql_close($db);
  30. ?>
  31.  
Expand|Select|Wrap|Line Numbers
  1. </table>
  2.  
When I run this, I get the following:

The echo of $query shows that the three values I am trying to pass do not get passed - for example, if I select Facility it shows
SELECT fac_id FROM jos_incidents_combined WHERE 1=1 AND fac_id=fac_id

This of course causes the $result to be blank.

I then get the error:
Error retrieving data from db - this is from the $result var
Error: No database selected

I have checked and rechecked my code for the db connection, and it is functioning. Also, when I paste the $query in phpmyadmin without the AND part, it runs successfully.

Is the problem just the fact that I'm not passing the values or does the 'No database selected' indicate a second problem as well?

What changes do I need to do to my code to get the variables passed?

Thanks - I apologize for the large content - but I have re-read some of the PHP materials and tried a few changes but nothing seems to work.

TIA,

jej1216
Jun 4 '07 #1
Share this Question
Share on Google+
6 Replies


ronnil
Expert 100+
P: 134
sounds like you are missing a mysql_select_db('db_name'); statement, but you're not. :)

You say that if you select facility it returns 0 rows? is that with other options than the hardcoded one? (with value=""), you shouldn't be getting any result if it's that one. (since fac_id in the query will then be "")

Why do you use 1=1 in your SQL statement?
Jun 4 '07 #2

P: 40
sounds like you are missing a mysql_select_db('db_name'); statement
Well, I feel like an idiot. That's exactly what was missing.

Thanks,

jej1216
Jun 4 '07 #3

ronnil
Expert 100+
P: 134
lol... think it's getting late here... you got two files :P

well... congrats on solving the problem :)
Jun 4 '07 #4

P: 40
It definitely was late. The missing a mysql_select_db('db_name') got rid of the error about the database, but it's still not passing the variables from the first php page to the second.

I get this as the resulting SQL statement:
"query: SELECT fac_id, room_descr, person_type, severity FROM jos_incidents_combined WHERE 1=1 AND fac_id=fac_id"

It's probably a quote thing, but why isn't the variable from the first php page not getting passed?

Also, I use WHERE 1=1 so the other AND statements can be in any combination. Apparently, WHERE 1=1 is a way to run the query whether the user picks none, one, two, or three search variables.
Jun 5 '07 #5

P: 40
I've almost got it -- I changed to the following code in the 1st php:
Expand|Select|Wrap|Line Numbers
  1. while ($myrow = mysql_fetch_array($result))
  2.    {
  3.    echo "<option value='$myrow[fac_id]'>".$myrow[fac_id]."</option>\n";
  4.    }
  5. ?>
  6.  
For fac_id, which is numeric, this works - the variable is passed and the query results are correct.

Now I have a new error. When the variable to be passed is character, I need to know how to change the following code to enclose it in quotes for the select statement:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. while ($myrow2 = mysql_fetch_array($result2))
  3.    {
  4.    echo "<option value='$myrow2[person_type]'>".$myrow2[person_type]."</option>\n";
  5.    }
  6. ?>
  7.  
This code returns this SQL:
SELECT fac_id, room_descr, person_type, severity FROM incidents WHERE 1=1 AND person_type =Outpatient
and this message:
"Error: Unknown column 'Outpatient' in 'where clause'"

How do I get the php code to enclose the value "Outpatient?"

thanks,

jej1216
Jun 5 '07 #6

P: 40
Since this is a new problem, I have started a new thread for how to add single quotes to a passed value.

Thanks,

jej1216
Jun 5 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.