drinix Posted July 22, 2008 Share Posted July 22, 2008 I have a php search script with a blank field (to type the company) and a drop down menu (name of US states) When I do a search all the records of the table is displayed, just as shown below. The desired output would be to display fld_companyName, fld_address, fld_city, fld_zip, fld_warnState, fld_numEmployees and fld_termDate of the chosen company and the state specified. How can I achieve this? Thanks fld_companyName fld_address fld_city fld_zip fld_warnState fld_numEmployees fld_termDate Dell 1 Main Street Trenton 12345 NJ 10 2008-06-18 Apple 2 Market Street Albany 23456 NY 20 2008-01-27 HP 4 State Street Hartford 34567 CT 35 2008-03-16 etc. Here is the script: <?php $hostname = "localhost"; $username = "dbuser"; $password = "password"; $usertable = "dbtable"; $dbName = "testdb"; if (!mysql_connect($hostname, $username, $password)) die("Can't connect to database"); if (!mysql_select_db($dbName)) die("Can't select database"); $result = mysql_query("SELECT * FROM {$usertable} "); if (!$result) { die("Query to show fields from table failed"); } $fields_num = mysql_num_fields($result); echo "<h1>Table: {$usertable}</h1>"; echo "<table border='1'><tr>"; for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; while($row = mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ?> Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/ Share on other sites More sharing options...
trq Posted July 22, 2008 Share Posted July 22, 2008 SELECT * FROM {$usertable} WHERE fld_warnState = 'NY'; Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596165 Share on other sites More sharing options...
drinix Posted July 22, 2008 Author Share Posted July 22, 2008 After editing the script, when searching other companies located in different states, the following error "Query to show fields from table failed". Question #2 - What changes of the script have to be made to query the db just for 2 or 3 columns instead of the 7 columns? Thanks Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596176 Share on other sites More sharing options...
trq Posted July 22, 2008 Share Posted July 22, 2008 Don't use SELECT *, use SELECT filedname1,fieldname2 etc etc Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596181 Share on other sites More sharing options...
drinix Posted July 22, 2008 Author Share Posted July 22, 2008 Thanks for answering question #2 For whatever queries I perform (different company and state), I am getting only the NY record. Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596185 Share on other sites More sharing options...
trq Posted July 22, 2008 Share Posted July 22, 2008 Post your current code. Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596500 Share on other sites More sharing options...
drinix Posted July 22, 2008 Author Share Posted July 22, 2008 This is the search script: <?php error_reporting(E_ALL); if (!isset($_POST['Submit'])) { ?> <form action="results.php" method="post" action="<?=$PHP_SELF?>"> Search for the company: <input type="text" name="search"> <select name="fld_warnState" id="fld_warnState"> <option value="-1">-- Choose state --</option> <option value="fld_warnState"></option> <option value="AK">AK</option> <option value="AL">AL</option> <option value="AR">AR</option> <option value="AZ">AZ</option> <option value="CA">CA</option> <option value="CO">CO</option> <option value="CT">CT</option> <option value="DE">DE</option> <option value="FL">FL</option> <option value="GA">GA</option> <option value="HI">HI</option> <option value="IA">IA</option> <option value="ID">ID</option> <option value="IL">IL</option> <option value="IN">IN</option> <option value="KS">KS</option> <option value="KY">KY</option> <option value="LA">LA</option> <option value="MA">MA</option> <option value="MD">MD</option> <option value="ME">ME</option> <option value="MI">MI</option> <option value="MN">MN</option> <option value="MO">MO</option> <option value="MS">MS</option> <option value="MT">MT</option> <option value="NC">NC</option> <option value="ND">ND</option> <option value="NE">NE</option> <option value="NH">NH</option> <option value="NJ">NJ</option> <option value="NM">NM</option> <option value="NV">NV</option> <option value="NY">NY</option> <option value="OH">OH</option> <option value="OK">OK</option> <option value="OR">OR</option> <option value="PA">PA</option> <option value="RI">RI</option> <option value="SC">SC</option> <option value="SD">SD</option> <option value="TN">TN</option> <option value="TX">TX</option> <option value="UT">UT</option> <option value="VA">VA</option> <option value="VT">VT</option> <option value="WA">WA</option> <option value="WI">WI</option> <option value="WV">WV</option> <option value="WY">WY</option> </select> <input type="Submit" value="Submit" name="Submit"> </form> <?php } else { $hostname = "localhost"; $username = "dbuser"; $password = "password"; $dbName = "testdb"; $connect = mysql_connect($hostname, $username, $password) or die ("Unable to connect to host"); mysql_select_db($dbName) or die ("Unable to connect to database"); mysql_close($connect); } ?> This is the results script: <?php $hostname = "localhost"; $username = "dbuser"; $password = "password"; $usertable = "dbtable"; $dbName = "testdb"; if (!mysql_connect($hostname, $username, $password)) die("Can't connect to database"); if (!mysql_select_db($dbName)) die("Can't select database"); $result = mysql_query("SELECT fld_companyName, fld_warnState, fld_numEmployees FROM {$usertable} WHERE fld_warnState = 'NY' "); if (!$result) { die("Query to show fields from table failed"); } $fields_num = mysql_num_fields($result); echo "<h1>Table: {$usertable}</h1>"; echo "<table border='1'><tr>"; for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; while($row = mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ?> Thanks Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-596528 Share on other sites More sharing options...
drinix Posted July 25, 2008 Author Share Posted July 25, 2008 Now when I hit the search button, all the table records get displayed. The ideal results would be to display any data on a given row as long as the the company typed and the state selected are searched. Can somebody please take a look at this code. Thanks Search for the company: <input type="text" name="find" /> in <select name="fld_warnState"> // Table Field <option value="" selected="selected">Choose a State</option> <option value="AL">Alabama</option> <option value="AK">Alaska</option> <option value="AZ">Arizona</option> <option value="AR">Arkansas</option> <option value="CA">California</option> <option value="CO">Colorado</option> <option value="CT">Connecticut</option> <option value="DE">Delaware</option> <option value="DC">District Of Columbia</option> <option value="FL">Florida</option> <option value="GA">Georgia</option> <option value="HI">Hawaii</option> <option value="ID">Idaho</option> <option value="IL">Illinois</option> <option value="IN">Indiana</option> <option value="IA">Iowa</option> <option value="KS">Kansas</option> <option value="KY">Kentucky</option> <option value="LA">Louisiana</option> <option value="ME">Maine</option> <option value="MD">Maryland</option> <option value="MA">Massachusetts</option> <option value="MI">Michigan</option> <option value="MN">Minnesota</option> <option value="MS">Mississippi</option> <option value="MO">Missouri</option> <option value="MT">Montana</option> <option value="NE">Nebraska</option> <option value="NV">Nevada</option> <option value="NH">New Hampshire</option> <option value="NJ">New Jersey</option> <option value="NM">New Mexico</option> <option value="NY">New York</option> <option value="NC">North Carolina</option> <option value="ND">North Dakota</option> <option value="OH">Ohio</option> <option value="OK">Oklahoma</option> <option value="OR">Oregon</option> <option value="PA">Pennsylvania</option> <option value="RI">Rhode Island</option> <option value="SC">South Carolina</option> <option value="SD">South Dakota</option> <option value="TN">Tennessee</option> <option value="TX">Texas</option> <option value="UT">Utah</option> <option value="VT">Vermont</option> <option value="VA">Virginia</option> <option value="WA">Washington</option> <option value="WV">West Virginia</option> <option value="WI">Wisconsin</option> <option value="WY">Wyoming</option> </select> <input type="hidden" name="searching" value="yes" /> <input type="submit" value="search" name="search" /> </form> <?php $searching = $_POST['searching']; $find = $_POST['find']; ini_set("display_errors","2"); ERROR_REPORTING(E_ALL); //Displayed only when the form has been submitted if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If no search term is typed an error is displayed if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } $hostname = "localhost"; $username = "dbuser"; $password = "password"; $usertable = "dbtable"; $dbName = "databasename"; if (!mysql_connect($hostname, $username, $password)) die("Can't connect to database"); if (!mysql_select_db($dbName)) die("Can't select database"); // Perform a bit of filtering $find = strtoupper($find); // Change characters to UPPER case $find = strip_tags($find); // Removes out any code that may have been entered previously $find = trim ($find); // Removes out all the whitespace //Search based on the search term, in the field the user specified $data = mysql_query("SELECT * FROM {$usertable}"); //Display the results while($result = mysql_fetch_array( $data )) { echo $result['fld_companyName']; echo " "; echo $result['fld_address']; echo "<br>"; echo $result['fld_city']; echo "<br>"; echo $result['fld_zip']; echo "<br>"; echo $result['fld_warnState']; echo "<br>"; echo $result['fld_numEmployees']; echo "<br>"; echo $result['fld_termDate']; echo "<br>"; echo "<br>"; } //Counting the number or results - If there is no results, a message is displayed $anymatches=mysql_num_rows($data); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } ?> Link to comment https://forums.phpfreaks.com/topic/115953-php-results-script-accessing-mysql/#findComment-599782 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.