sstoveld Posted April 6, 2009 Share Posted April 6, 2009 hey guys, im having some trouble here. what i need to do is let the user search the mysql database for states related to the search term. eg: user searches "AL" results of search: ALABAMA ALASKA as of right now, when i hit search, nothing happens here's my code: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Insert Data From Text File</title> <link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <div id="wrapper"> <div id="banner"> <h1><center><a href="../php6/index.php">Inserting Data From A Text File</a></center></h1> </div><!-- banner --> <div id="mainContent"> <h2>Type your search query below</h2> <?php ######################################################################################### # This PHP script will allow the user to search for values from an imported .CSV file # # Coded by: Steve Stoveld # # INF 216: Programming for the Web II # # Due: 4/5/09 # # PHP Assignment 6 # ######################################################################################### // Connect to the server // $dbcnx = @mysql_connect('*******', '*******', '*******') or die ('I cannot connect to the database because: ' .mysql_error()); // Connect to the database // mysql_select_db('sstoveld', $dbcnx); if (!@mysql_select_db('sstoveld')) { exit ('<p>Error in query.'. mysql_error(). '<p>'); } // Get States // $states = @mysql_query('SELECT id, states FROM states_table'); if (!$states) { exit("<p>Unable to get States list</p>"); } // Search Form // ?> <form action="index.php" method="post"> <h2>Search</h2> <p>State: <input name="state" type="text" value="any"></p> <input type="hidden" name="query" value=1 /> <p><input name="search" type="submit" value="Search" /></p> </form> <?php // Search States // if (isset($_GET['query'])) { $states=$_GET['states']; $select = "SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id"; $where = " where 1=1"; // States Match // if ($states != "any") { $where .=" AND states LIKE '%$states%'"; } // SQL Query // $QUERY = $select.$where; $sql=@mysql_query($QUERY); if (!$sql) { exit('<p>Error Occured'.mysql_error().'</p>'); } // Table containing search results // echo "<h2>Query and result of search</h2>"; echo "<p>".$QUERY."</p>"; echo "<p><table>"; while ($state = mysql_fetch_array($sql)) { $states = htmlentities($state['states']); echo "<tr><td>".$states."</td></tr>"; } ?> </table></p> <?php } // Clear the tables // if (isset($_POST['clear'])) { mysql_query("TRUNCATE TABLE `states_table`"); mysql_query("TRUNCATE TABLE `wealth_table`"); if (!mysql_query) { echo "<p>Error Occurred: ".mysql_error()."</p>"; } else { echo "<p>Tables successfully cleared</p>"; } } // Load the wealth .CSV file // if (isset($_POST['loadwealth'])) { $text = file("wealth.csv"); // This is the .CSV file // $headers=array_shift($text); // This will get rid of the column headers // foreach ($text as $line) { $line=trim($line); // This gets rid of the line endings // $pieces = explode(",", $line); // Breaks into sub-arrays along the commas // $pieces = str_replace('"', '', $pieces); // Gets rid of double quotes, if there are any // $pieces = str_replace("'", "\'", $pieces); // This will manage the single quotes, if there are any // $states=$pieces[0]; $millionaires=$pieces[1]; $dollars=$pieces[2]; $states_id=$pieces[3]; // Query the states_table table to get the states id // $get_states=@mysql_query("SELECT id FROM states_table WHERE states='$states'"); if (!$get_states) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_states_row = mysql_fetch_array($get_states); $states_id = $get_states_row['id']; // Insert the state into the table // $sql="INSERT INTO states_table SET states='$states'"; if (!@mysql_query($sql)) { echo "<p>There was an error adding the state: ".mysql_error()."</p>"; } $states_id = mysql_insert_id(); // Query the wealth_table table to get the wealth id // $get_wealth=@mysql_query("SELECT wealth_id, states_id FROM wealth_table WHERE millionaires='$millionaires' && dollars='$dollars'"); if (!$get_wealth) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_wealth_row = mysql_fetch_array($get_wealth); $wealth_id = $get_wealth_row['wealth_id']; // Insert the wealth info into the table // $sql2="INSERT INTO wealth_table SET states_id='$states_id', millionaires='$millionaires', dollars='$dollars'"; if (!@mysql_query($sql2)) { echo "<p>There was an error adding the wealth info: ".mysql_error()."</p>"; } } } // Select the data from the MySQL database // $queryresult = @mysql_query('SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id'); if (!$queryresult) { exit ('<p>There was an error in the MySQL Query:'. mysql_error().'</p>'); } // Make an HTML table to hold the data from the .CSV // ?> <h2>Hit the Load button below to import the data from the Excel CSV file.</h2> <p> <table> <form name="loadwealth" action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post"> <tr> <td><strong>State</strong></td> <td><strong>Millionaires (x 1,000)</strong></td> <td><strong>Dollars (x 1,000,000)</strong></td> </tr> <?php while ($row = mysql_fetch_array($queryresult)) { echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>'); } ?> <input type="submit" value="Load" name="loadwealth" /> <input type="submit" value="Reset" name="clear" /> </table> </form> </p> </div><!-- mainContent --> <div id="footer"> <p>Coded and designed by: <a href="mailto:sstoveld@gmail.com">Steve Stoveld</a></p> </div><!-- footer --> </div><!-- wrapper --> </body> </html> pastebin, if its easier to read: http://pastebin.com/m2dc99357 and here's the link to the page that im having a problem with: http://stovelddesign.com/INF216/php6/index.php and if needed, here's my assignment: http://research.informatics.buffalo.edu/Faculty/Scott/INF216/course/9-Search.php any help is appreciated, ive been working on this for 2 days and i cant seem to get it right Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 Take a look into the Porter Stemmer algorithm, it is a great search tool that will search for like terms. Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 Take a look into the Porter Stemmer algorithm, it is a great search tool that will search for like terms. sorry, i dont think i would be allowed to use something like that, we're supposed to be creating our own search script. its for a class assignment. i took a look at what i think you are talking about for fun, and i dont understand it at all lol. this is what i found http://tartarus.org/~martin/PorterStemmer/php.txt Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 It looks like you are posting variables yet you are using GET, try $_POST instead. Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 It looks like you are posting variables yet you are using GET, try $_POST instead. well i feel stupid i always mix them up. ok well it almost works now, i feel it. when i search, it displays a table with EVERY state, instead of the states related to my search term. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Insert Data From Text File</title> <link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <div id="wrapper"> <div id="banner"> <h1><center><a href="../php6/index.php">Inserting Data From A Text File</a></center></h1> </div><!-- banner --> <div id="mainContent"> <h2>Type your search query below</h2> <?php ######################################################################################### # This PHP script will allow the user to search for values from an imported .CSV file # # Coded by: Steve Stoveld # # INF 216: Programming for the Web II # # Due: 4/5/09 # # PHP Assignment 6 # ######################################################################################### // Connect to the server // $dbcnx = @mysql_connect('******', '*******', '*******') or die ('I cannot connect to the database because: ' .mysql_error()); // Connect to the database // mysql_select_db('sstoveld', $dbcnx); if (!@mysql_select_db('sstoveld')) { exit ('<p>Error in query.'. mysql_error(). '<p>'); } // Get States // $states = @mysql_query('SELECT id, states FROM states_table'); if (!$states) { exit("<p>Unable to get States list</p>"); } // Search Form // ?> <form action="index.php" method="post"> <h2>Search</h2> <p>State: <input name="state" type="text" value="any"></p> <input type="hidden" name="query" value=1 /> <p><input name="search" type="submit" value="Search" /></p> </form> <?php // Search States // if (isset($_POST['query'])) { $states=$_POST['states']; $select = "SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id"; $where = " where 1=1"; // States Match // if ($states != "any") { $where .=" AND states LIKE '%$state%'"; } // SQL Query // $QUERY = $select.$where; $sql=@mysql_query($QUERY); if (!$sql) { exit('<p>Error Occured'.mysql_error().'</p>'); } // Table containing search results // echo "<h2>Query and result of search</h2>"; echo "<p>".$QUERY."</p>"; echo "<p><table>"; while ($state = mysql_fetch_array($sql)) { $states = htmlentities($state['states']); echo "<tr><td>".$states."</td></tr>"; } ?> </table></p> <?php } // Clear the tables // if (isset($_POST['clear'])) { mysql_query("TRUNCATE TABLE `states_table`"); mysql_query("TRUNCATE TABLE `wealth_table`"); if (!mysql_query) { echo "<p>Error Occurred: ".mysql_error()."</p>"; } else { echo "<p>Tables successfully cleared</p>"; } } // Load the wealth .CSV file // if (isset($_POST['loadwealth'])) { $text = file("wealth.csv"); // This is the .CSV file // $headers=array_shift($text); // This will get rid of the column headers // foreach ($text as $line) { $line=trim($line); // This gets rid of the line endings // $pieces = explode(",", $line); // Breaks into sub-arrays along the commas // $pieces = str_replace('"', '', $pieces); // Gets rid of double quotes, if there are any // $pieces = str_replace("'", "\'", $pieces); // This will manage the single quotes, if there are any // $states=$pieces[0]; $millionaires=$pieces[1]; $dollars=$pieces[2]; $states_id=$pieces[3]; // Query the states_table table to get the states id // $get_states=@mysql_query("SELECT id FROM states_table WHERE states='$states'"); if (!$get_states) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_states_row = mysql_fetch_array($get_states); $states_id = $get_states_row['id']; // Insert the state into the table // $sql="INSERT INTO states_table SET states='$states'"; if (!@mysql_query($sql)) { echo "<p>There was an error adding the state: ".mysql_error()."</p>"; } $states_id = mysql_insert_id(); // Query the wealth_table table to get the wealth id // $get_wealth=@mysql_query("SELECT wealth_id, states_id FROM wealth_table WHERE millionaires='$millionaires' && dollars='$dollars'"); if (!$get_wealth) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_wealth_row = mysql_fetch_array($get_wealth); $wealth_id = $get_wealth_row['wealth_id']; // Insert the wealth info into the table // $sql2="INSERT INTO wealth_table SET states_id='$states_id', millionaires='$millionaires', dollars='$dollars'"; if (!@mysql_query($sql2)) { echo "<p>There was an error adding the wealth info: ".mysql_error()."</p>"; } } } // Select the data from the MySQL database // $queryresult = @mysql_query('SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id'); if (!$queryresult) { exit ('<p>There was an error in the MySQL Query:'. mysql_error().'</p>'); } // Make an HTML table to hold the data from the .CSV // ?> <h2>Hit the Load button below to import the data from the Excel CSV file.</h2> <p> <table> <form name="loadwealth" action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post"> <tr> <td><strong>State</strong></td> <td><strong>Millionaires (x 1,000)</strong></td> <td><strong>Dollars (x 1,000,000)</strong></td> </tr> <?php while ($row = mysql_fetch_array($queryresult)) { echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>'); } ?> <input type="submit" value="Load" name="loadwealth" /> <input type="submit" value="Reset" name="clear" /> </table> </form> </p> </div><!-- mainContent --> <div id="footer"> <p>Coded and designed by: <a href="mailto:sstoveld@gmail.com">Steve Stoveld</a></p> </div><!-- footer --> </div><!-- wrapper --> </body> </html> see something else wrong? EDIT: it seems the wildcard term isnt being passed on. here's what the query is looking like that is being sent SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id where 1=1 AND states LIKE '%%' Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 $where .=" AND states LIKE '%$state%'"; should be $where .=" AND states LIKE '%$states%'"; Missing the S on states Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 $where .=" AND states LIKE '%$state%'"; should be $where .=" AND states LIKE '%$states%'"; Missing the S on states lol i originally had it with the s on the end ($states) but it wasnt working, so i tried taking it off cause i always pick stupid variable names and confuse them, but still the same problem Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 $states=$_POST['states']; should be $states=$_POST['state']; lol your input field is name="state" Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 $states=$_POST['states']; should be $states=$_POST['state']; lol your input field is name="state" thanks so much! that did the trick! i feel pretty stupid. i've now gotta do a search form to search for the number of millionaires. im gonna give it a shot now, but ill likely be back asking for some more help thanks again, ill mark solved once i get the second search working Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 ok i think i almost have it, although i cant get it to display the state next to the number of millionaires in the results. here's my code: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Insert Data From Text File</title> <link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <div id="wrapper"> <div id="banner"> <h1><center><a href="../php6/index.php">Inserting Data From A Text File</a></center></h1> </div><!-- banner --> <div id="mainContent"> <h2>Type your search query below</h2> <?php ######################################################################################### # This PHP script will allow the user to search for values from an imported .CSV file # # Coded by: Steve Stoveld # # INF 216: Programming for the Web II # # Due: 4/5/09 # # PHP Assignment 6 # ######################################################################################### // Connect to the server // $dbcnx = @mysql_connect('******', '******', '*****') or die ('I cannot connect to the database because: ' .mysql_error()); // Connect to the database // mysql_select_db('sstoveld', $dbcnx); if (!@mysql_select_db('sstoveld')) { exit ('<p>Error in query.'. mysql_error(). '<p>'); } // Get States // $states = @mysql_query('SELECT id, states FROM states_table'); if (!$states) { exit("<p>Unable to get States list</p>"); } // Search Form // ?> <form action="index.php" method="post"> <h2>Search</h2> <p>State: <input name="state" type="text" value="any"></p> <input type="hidden" name="query" value=1 /> <p><input name="search" type="submit" value="Search" /></p> </form> <form action="index.php" method="post"> <p>Millionaires: <input name="millionaires" type="text" value="any" /></p> <input type="hidden" name="milquery" value="2" /> <p><input name="search2" type="submit" value="Search" /></p> </form> <?php // Search States // if (isset($_POST['query'])) { $states=$_POST['state']; $select = "SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id"; $where = " where 1=1"; // States Match // if ($states != "any") { $where .=" AND states LIKE '%$states%'"; } // SQL Query // $QUERY = $select.$where; $sql=@mysql_query($QUERY); if (!$sql) { exit('<p>Error Occured'.mysql_error().'</p>'); } // Table containing search results // echo "<h2>Query and result of search</h2>"; echo "<p>".$QUERY."</p>"; echo "<p><table>"; while ($state = mysql_fetch_array($sql)) { $states = htmlentities($state['states']); echo "<tr><td>".$states."</td></tr>"; } ?> </table></p> <?php } // Millionaires Search // if (isset($_POST['milquery'])) { $millionaires=$_POST['millionaires']; $select = "SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id"; $where = " where 1=1"; // Millionaires Match // if ($millionaires != "any") { $where .=" AND millionaires = '$millionaires'"; } // SQL Query // $QUERY = $select.$where; $sql=@mysql_query($QUERY); if (!$sql) { exit('<p>Error Occurred'.mysql_error().'</p>'); } // Table containing search results // echo "<h2>Query and result of search</h2>"; echo "<p>".$QUERY."</p>"; echo "<p><table>"; while ($millionaires = mysql_fetch_array($sql)) { $states = htmlentities($states['state']); $millionaires = htmlentities($millionaires['millionaires']); echo "<tr><td>".$states."</td>"; echo "<td>".$millionaires."</td></tr>"; } ?> </table></p> <?php } // Clear the tables // if (isset($_POST['clear'])) { mysql_query("TRUNCATE TABLE `states_table`"); mysql_query("TRUNCATE TABLE `wealth_table`"); if (!mysql_query) { echo "<p>Error Occurred: ".mysql_error()."</p>"; } else { echo "<p>Tables successfully cleared</p>"; } } // Load the wealth .CSV file // if (isset($_POST['loadwealth'])) { $text = file("wealth.csv"); // This is the .CSV file // $headers=array_shift($text); // This will get rid of the column headers // foreach ($text as $line) { $line=trim($line); // This gets rid of the line endings // $pieces = explode(",", $line); // Breaks into sub-arrays along the commas // $pieces = str_replace('"', '', $pieces); // Gets rid of double quotes, if there are any // $pieces = str_replace("'", "\'", $pieces); // This will manage the single quotes, if there are any // $states=$pieces[0]; $millionaires=$pieces[1]; $dollars=$pieces[2]; $states_id=$pieces[3]; // Query the states_table table to get the states id // $get_states=@mysql_query("SELECT id FROM states_table WHERE states='$states'"); if (!$get_states) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_states_row = mysql_fetch_array($get_states); $states_id = $get_states_row['id']; // Insert the state into the table // $sql="INSERT INTO states_table SET states='$states'"; if (!@mysql_query($sql)) { echo "<p>There was an error adding the state: ".mysql_error()."</p>"; } $states_id = mysql_insert_id(); // Query the wealth_table table to get the wealth id // $get_wealth=@mysql_query("SELECT wealth_id, states_id FROM wealth_table WHERE millionaires='$millionaires' && dollars='$dollars'"); if (!$get_wealth) { exit ('<p>There was an error in the query. '. mysql_error().'</p>'); } $get_wealth_row = mysql_fetch_array($get_wealth); $wealth_id = $get_wealth_row['wealth_id']; // Insert the wealth info into the table // $sql2="INSERT INTO wealth_table SET states_id='$states_id', millionaires='$millionaires', dollars='$dollars'"; if (!@mysql_query($sql2)) { echo "<p>There was an error adding the wealth info: ".mysql_error()."</p>"; } } } // Select the data from the MySQL database // $queryresult = @mysql_query('SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id'); if (!$queryresult) { exit ('<p>There was an error in the MySQL Query:'. mysql_error().'</p>'); } // Make an HTML table to hold the data from the .CSV // ?> <h2>Hit the Load button below to import the data from the Excel CSV file.</h2> <p> <table> <form name="loadwealth" action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post"> <tr> <td><strong>State</strong></td> <td><strong>Millionaires (x 1,000)</strong></td> <td><strong>Dollars (x 1,000,000)</strong></td> </tr> <?php while ($row = mysql_fetch_array($queryresult)) { echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>'); } ?> <input type="submit" value="Load" name="loadwealth" /> <input type="submit" value="Reset" name="clear" /> </table> </form> </p> </div><!-- mainContent --> <div id="footer"> <p>Coded and designed by: <a href="mailto:sstoveld@gmail.com">Steve Stoveld</a></p> </div><!-- footer --> </div><!-- wrapper --> </body> </html> its displaying the results of the millionaires fine, but i want to to display which state has that many millionaires Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 <?php while ($millionaires = mysql_fetch_array($sql)) { $states = htmlentities($states['state']); $millionaires = htmlentities($millionaires['millionaires']); echo "<tr><td>".$states."</td>"; echo "<td>".$millionaires."</td></tr>"; } ?> You are only using $millionaires for your sql fetch so you can only get data from it. try $millionaires['state'] to get your state. Since you are using a join I think it should work. Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 <?php while ($millionaires = mysql_fetch_array($sql)) { $states = htmlentities($states['state']); $millionaires = htmlentities($millionaires['millionaires']); echo "<tr><td>".$states."</td>"; echo "<td>".$millionaires."</td></tr>"; } ?> You are only using $millionaires for your sql fetch so you can only get data from it. try $millionaires['state'] to get your state. Since you are using a join I think it should work. you mean like this? - while ($millionaires = mysql_fetch_array($sql)) { $states = htmlentities($millionaires['state']); $millionaires = htmlentities($millionaires['millionaires']); echo "<tr><td>".$states."</td>"; echo "<td>".$millionaires."</td></tr>"; } that doesnt seem to work Quote Link to comment Share on other sites More sharing options...
Fruct0se Posted April 6, 2009 Share Posted April 6, 2009 looks like you need the s in states again $millionaires['states'] Quote Link to comment Share on other sites More sharing options...
sstoveld Posted April 6, 2009 Author Share Posted April 6, 2009 looks like you need the s in states again $millionaires['states'] thanks so much! working great now. i need to choose better variable names next time lol thanks again Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.