Solved

PHP/MySQL - Querying a database (adding a radio button as an option to sort)

Posted on 2009-06-28
18
959 Views
Last Modified: 2013-12-13
In my php code below, I am taking information from a html form that has multiple buttons.  The first one query's the database according to state.  The second one querys the database for all records.  
I need to add a radio button as an option to get this information sorted.  So if they click it, regardless of which button they press, I need the information to be sorted by one of the fields.  What is the best way to do this (im still in the learning process)?

Also - for the output of the data, regardless of which button they pressed, I need to display it in a data.  Is my while loop the best way to get this done?  Or is a for loop best used?

Thanks
<?php
 

$state = $_POST["state"];

$allrecords = $_POST["allrecords"];
 

$Connection = mysqli_connect("localhost", "username", "password", "databasename");
 

if(isset($_POST["state"])) {

	$query = "SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE state = '$state'";

	$result = mysql_query($query);

}
 

else if (isset($_POST["allrecords"])) {

	$query = "SELECT * FROM hill";

	$result = mysql_query($query);

}
 

while($data = mysql_fetch_array($result)) {

	echo "<table>"

	echo "</table>"

}

mysqli_close($Connection);
 

?>

Open in new window

0
Comment
Question by:techlinden
  • 9
  • 9
18 Comments
 
LVL 28

Accepted Solution

by:
gamebits earned 500 total points
ID: 24732566
Add to your form

Sort by
<input type="radio" name="sort" value="lastname"> Last Name
<br><input type="radio" name="sort" value="firstname"> First Name
<br><input type="radio" name="sort" value="areaCode"> Area Code
<br><input type="radio" name="sort" value="telephoneNumber"> Telephone Number

Sort options
<p><input type="radio" name="ascdsc" value="ASC" checked=checked> Ascending
<br><input type="radio" name="sort" value="DESC"> Descending

<?php
 
$state = $_POST["state"];
$allrecords = $_POST["allrecords"];
$sort = $_POST['sort'];
$ascdsc = $_POST['ascdsc'];
 
$Connection = mysqli_connect("localhost", "username", "password", "databasename");
 
if(isset($_POST["state"])) {
      $query = "SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE state = '$state'";
      
        if(isset($sort)){
         $query .= " ORDER BY $sort $ascdsc";
                        }

      $result = mysql_query($query);
}
 
else if (isset($_POST["allrecords"])) {
      $query = "SELECT * FROM hill";

        if(isset($sort)){
         $query .= " ORDER BY $sort $ascdsc";
                        }

      $result = mysql_query($query);
}
 
while($data = mysql_fetch_array($result)) {
      echo "<table>"
      echo "</table>"
}
mysqli_close($Connection);
 
?>

And of course I can explain if needed
0
 

Author Closing Comment

by:techlinden
ID: 31597705
Cool
0
 

Author Comment

by:techlinden
ID: 24733106
Quick question:

What if nothing is selected or the database has no information for what is selected?
Also - i'm getting the following errors:

Notice: Undefined index: state in C:\wamp\www\hill\query.php on line 19

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\wamp\www\hill\query.php on line 41

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php on line 44

Should I assign the string as empty?  
<?php
 

$state = $_POST["state"];

$allrecords = $_POST["allrecords"];

$sort = $_POST["sort"];

$ascdsc = $_POST["ascdsc"];
 

$Connection = mysqli_connect("localhost", "username", "password", "hill");
 

if(isset($_POST["state"])) {

	$query = "SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE state = '$state'";
 

		if(isset($sort)) {

			$query .="ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($query);

}
 

else if (isset($_POST["allrecords"])) {

	$query = "SELECT * FROM hill";
 

		if(isset($sort)) {

			$query .="ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($query);

}
 

while($data = mysql_fetch_array($result)) {

	echo "<font color='#a3b9cb'><table align='center' bgcolor='#333333' border='2' width='800' cellpadding='2' cellspacing='2'>";

	echo "<tr><td>$data[state]</td></tr>";

	echo "</table>";

}
 

mysqli_close($Connection);
 

?>

Open in new window

0
 
LVL 28

Expert Comment

by:gamebits
ID: 24733155
If you have only 2 possible choices (state or allrecords) you do not need 2 if(isset statement you should go with if(isset($state)){ do the query}else{do the other query} which would be all records so the query will always return something.

------------------------------------------------------------------------------------------------------------------
replacing
$result = mysqli_query($query);

by
$result = mysqli_query($query, $Connection);

should take care of the notice
 "Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\wamp\www\hill\query.php on line 41"

--------------------------------------------------------------------------------------

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php on line 44

just mean the query did not work, could be related to the notice above.
0
 

Author Comment

by:techlinden
ID: 24733270
Ah - that makes quite a bit of sense with the isset.  It's not needed twice!

I changed the $result = mysqli_query and switched the variables to ($Connection, $query) rather than ($query, $Connection) - and was able to lose that error message - but the one below it still exists.

"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php on line 44"

Also, I noticed that if I don't select one of the radio buttons, then an error comes up as undefined.  If I select the "query by state" buttons, the allrecords comes up undefined.  And if I select the allrecords button, the query by state button comes up undefined.

Thinking about this - I probably need to include the statements:
$state = $_POST["state"];
$allrecords = $_POST["allrecords"];
$sort = $_POST["sort"];
$ascdsc = $_POST["ascdsc"];
in the if statements, corresponding with the right statements, correct?

Thank you for all your help.  I really appreciate it.
0
 

Author Comment

by:techlinden
ID: 24733288
Yes, that worked - if I build the statements into the if statement, that fixes the issue of undefined statements, unless of course, nothing is selected.  So I need a statement that says, if there is no value, echo "<p>No information available</p>"

This is the only error i'm getting at this point:

"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php on line 44"


0
 
LVL 28

Expert Comment

by:gamebits
ID: 24733300
The easiest way to get rid of the notice is to turn them off but it is not the right way to code so what we will do is assign a default value to the variables, replace

$state = $_POST["state"];
$allrecords = $_POST["allrecords"];
$sort = $_POST['sort'];
$ascdsc = $_POST['ascdsc'];

with

if(isset($_POST['state'])){$state = $_POST['state'];}
if(isset($_POST['allrecords'])){$allrecords = $_POST['allrecords'];}
if(isset($_POST['sort'])){$sort = $_POST['sort'];}
if(isset($_POST['ascdsc'])){$ascdsc = $_POST['ascdsc'];}
0
 
LVL 28

Expert Comment

by:gamebits
ID: 24733326
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php on line 44"

like I said mean the query is not working amongst the reason it could be the connection to the database is not made properly, we have a misspeled field name in the query (or table name) etc.

One way to find out how your query look is to use an echo statement

echo "THE QUERY: $query";

this way you can see what variable are being past 9or not) to the query, if you have access to the db directly (with phpmyadmin maybe) you can copy and paste the query return directly and see what error you are getting if any.
0
 

Author Comment

by:techlinden
ID: 24741427
Ah....you said the table name.  That might be the issue.  I have it connecting to the database called hill.  The table name is "tbl telephone directory" -  does the specific table name need to be called?  And if so, where?
<?php
 

if(isset($_POST['state'])){$state = $_POST['state'];}

if(isset($_POST['allrecords'])){$allrecords = $_POST['allrecords'];}

if(isset($_POST['sort'])){$sort = $_POST['sort'];}

if(isset($_POST['ascdsc'])){$ascdsc = $_POST['ascdsc'];}
 

$Connection = mysqli_connect("localhost", "username", "password", "hill");
 

if(isset($_POST["state"])) {	

	$query = "SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE 'state' = '$state'";
 

		if(isset($sort)) {

			$query .="ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($Connection, $query);

}
 

else if (isset($_POST["allrecords"])) {

	$query = "SELECT * FROM hill";
 

		if(isset($sort)) {

			$query .="ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($Connection, $query);

}
 

while($data = mysqli_fetch_array($result)) {

	echo "<table>";

	echo "<tr><td>$data[state]</td></tr>";

	echo "</table>";

}
 

mysqli_close($Connection);
 

?>

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:techlinden
ID: 24741467
I echoed the query, like you suggested and got:

The Query: SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE 'state' = 'Get State Records'ORDER BY lastname ASC
0
 
LVL 28

Expert Comment

by:gamebits
ID: 24741654
You connect to a database but you select from table ( a database can have multiple tables)

so your query should eb

SELECT lastName, firstName, areaCode, telephoneNumber FROM tbl telephone directory WHERE 'state' = '$state'";
0
 

Author Comment

by:techlinden
ID: 24741820
my warning is different, now.  lol

Ok - so when I click the "get state records" button - my query looks like:

My Query: SELECT lastName, firstName, areaCode, telephoneNumber FROM tbl telephone directory WHERE state = 'Get State Records'ORDER BY firstname DESC

But when I select the "all records" button, my query looks like:

My Query: SELECT * FROM tbl telephone directoryORDER BY firstname DESC

But my error message looks like:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\hill\query.php on line 46

I apologize - and really appreciate your help.
0
 
LVL 28

Expert Comment

by:gamebits
ID: 24743689
For one thing you should add a space before ORDER

$query .=" ORDER BY $sort $ascdsc";

instead of

$query .="ORDER BY $sort $ascdsc";

so your query will look like this

SELECT * FROM tbl telephone directory ORDER BY firstname DESC

0
 
LVL 28

Expert Comment

by:gamebits
ID: 24745163
I do not mind at all to keep helping you with this question but next time you ask a new question you should wait a little before closing it, this will give a chance to get more expert involved.
0
 

Author Comment

by:techlinden
ID: 24750228
Gotcha!  I'll definitely remember not to close it.
I added the space, and also learned that if I add a ` around the name of the table, I was able to remove the error code.  The name of the table is tbl telephone directory (which includes spaces - big no no)  So by putting the ` - i was able to solve the error issue.

The only issue that i'm having now is not getting data returned.  As you suggested, I am using the echo to echo $query, and i'm still getting this error when I select the get state records button::

My Query: SELECT lastName, firstName, areaCode, telephoneNumber FROM `tbl telephone directory` WHERE state = 'Get State Records' ORDER BY firstname ASC

And when I select the get all records button:
My Query: SELECT * FROM `tbl telephone directory` ORDER BY firstname ASC

However, the state is showing up in the table.
<?php
 

if(isset($_POST['state'])){$state = $_POST['state'];}

if(isset($_POST['allrecords'])){$allrecords = $_POST['allrecords'];}

if(isset($_POST['sort'])){$sort = $_POST['sort'];}

if(isset($_POST['ascdsc'])){$ascdsc = $_POST['ascdsc'];}
 

$Connection = mysqli_connect("localhost", "username", "password", "hill");
 

if(isset($_POST["state"])) {	

	$query = "SELECT lastName, firstName, areaCode, telephoneNumber FROM `tbl telephone directory` WHERE state = '$state'";
 

		if(isset($sort)) {

			$query .=" ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($Connection, $query);

}
 

else if (isset($_POST["allrecords"])) {

	$query = "SELECT * FROM `tbl telephone directory`";
 

		if(isset($sort)) {

			$query .=" ORDER BY $sort $ascdsc";

			}

	$result = mysqli_query($Connection, $query);

}
 

while($data = mysqli_fetch_array($result)) {

	echo "<table>";

	echo "<tr><td>$data[state]</td></tr>";

	echo "</table>";

}
 

mysqli_close($Connection);
 

?>

Open in new window

0
 
LVL 28

Expert Comment

by:gamebits
ID: 24750304
I need to see the form you are using, obviously it is not passsing the right information to the query.
0
 

Author Comment

by:techlinden
ID: 24750359
Sure
<body>
 

<font color='#a3b9cb'>
 

<h1 align="center">Query Form</h1>
 

<form action="query.php" method="post" enctype="application/x-www-form-urlencoded">

<table align='center' bgcolor='#333333' border='2' width='800'cellpadding="2" cellspacing="2">

<tr><td colspan="2">Sort by: 

<br /><center><input type="radio" name="sort" value="lastname"> Last Name

<input type="radio" name="sort" value="firstname"> First Name

<input type="radio" name="sort" value="areaCode"> Area Code

<input type="radio" name="sort" value="telephoneNumber"> Telephone Number

</center></td></tr>

<tr><td colspan="2">Sort Options:

<br /><center><input type="radio" name="ascdsc" value="ASC"> Ascending

<br><input type="radio" name="ascdsc" value="DESC"> Descending

</center></td></tr>
 

<tr><td><p>Select Records From The State of: <select name="State">

	<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="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>

</p></select></td><td><input type="submit" value="Get State Records" name="state" align="right"/></td></tr>
 

<tr><td><p>Select All Records:</p></td><td><input type="submit" value="Get All Records" name="allrecords"/></td></tr>
 
 

</table>

</form>

Open in new window

0
 
LVL 28

Expert Comment

by:gamebits
ID: 24750829
this is what we  are looking for from the form

if(isset($_POST['state'])){$state = $_POST['state'];}

This is the select field

<select name="State"> <<<< notice the Capital S

This is the submit button

<input type="submit" value="Get State Records" name="state" align="right"/> <<<<notice the name, that's why you are getting Get State Records in your query

Change the submit button for this

<input type="submit" value="Get State Records" name="submit" align="right"/>

and adjust the the name of the select button

<select name="state">

in php variable are case sensitive state, State, STATE are not the same.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now