cedtech31 Posted March 22, 2007 Share Posted March 22, 2007 I need help with my logic statement with a simple search engine on a MySQL table. I have three form text input fields named f_name, l_name and dept. I am using the GET method to gather data the user enters in each input field. So far I have been able to build a check to see that the submit has been clicked but I am having trouble with assembling the SQL statement because I need for the search engine to be able to filter the results based on the information provided in 1-3 of the fields. I could use if statements with some and clauses but that would mean that I would have to account for each combination. Something like If(($_GET[‘f_name’] != “”) || ($_GET[‘l_name’] !=) || ($_GET[‘dept] !=)){ $sql = "SELECT * FROM facform WHERE f_name LIKE '$_GET[f_name]', l_name LIKE '$_GET[‘l_name]', dept LIKE '$_GET[dept]' ”; } The problem with this is that I would have to get every combination and it’s not very scalable. How can I create this SQL statement dynamically? <?php if (array_key_exists('submit', $_GET)) { //SQL statement needed $db_name = db_test; $conn = mysql_connect('localhost', 'username', password'') or die(mysql_error()); $db = mysql_select_db($db_name, $conn); $result = mysql_query($sql, $conn) or die(mysql_error()); } ?> Can someone help be with my logic and solving this issue with limited steps? I have been using PHP for 2 weeks so please can you keep it simple Thanks Link to comment https://forums.phpfreaks.com/topic/43875-simple-filter-of-database-with-_get-and-select-sql/ Share on other sites More sharing options...
cedtech31 Posted March 22, 2007 Author Share Posted March 22, 2007 Thanks I figured it out $query = "SELECT * FROM table1 WHERE 1"; foreach ($_GET as $indexName => $arrayValue) { $query .= "AND $indexName LIKE '$arrayValue%'"; } Link to comment https://forums.phpfreaks.com/topic/43875-simple-filter-of-database-with-_get-and-select-sql/#findComment-213000 Share on other sites More sharing options...
cedtech31 Posted March 22, 2007 Author Share Posted March 22, 2007 <?php if (array_key_exists('submit', $_GET)){ include('includes/connection.inc.php'); $conn = dbConnect(); $sql = "SELECT * FROM facform WHERE 1"; foreach ($_GET as $indexName => $arrayValue) { if ($indexName != 'submit' && ($arrayValue != '') ) { $sql .= " AND $indexName LIKE '$arrayValue%'"; } } $result = mysql_query($sql, $conn) or die(mysql_error()); } ?> then in my the body of the PHP I have some XHTML for a table <table> <tr> <th>First Name</th> <th>Last Name</th> <th>Department</th> <tr> <?php while($row = @mysql_fetch_assoc($result)) { ?> <tr> <td><?php echo $row['f_name']; ?></td> <td><?php echo $row['l_name']; ?></td> <td><?php echo $row['dept']; ?></td> <td><?php echo '<a href="#">EDIT</a>'; ?></td> </tr> <?php } ?> </table> The issue I am having is if I leave off @ infront of @mysql_fetch_assoc($result)) I get Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in is there a better way to correct this issue? also how can I hide the TH tag and their connects until the $_GET submit button has been press? Link to comment https://forums.phpfreaks.com/topic/43875-simple-filter-of-database-with-_get-and-select-sql/#findComment-213098 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.