Jump to content

php results script accessing MySql


drinix

Recommended Posts

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

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

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

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;
}
?> 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.