We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

techlinden
techlinden asked
on
Medium Priority
1,116 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

Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Cool

Author

Commented:
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

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.

Author

Commented:
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"


CERTIFIED EXPERT

Commented:
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'];}
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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'";

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

CERTIFIED EXPERT

Commented:
I need to see the form you are using, obviously it is not passsing the right information to the query.

Author

Commented:
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

CERTIFIED EXPERT

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.