PHP/MySQL - Querying a database (adding a radio button as an option to sort)
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
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);
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\wamp\www\hill\query.php
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php
Should I assign the string as empty?
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
Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\wamp\www\hill\query.php
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php
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);
?>
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
--------------------------
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php
just mean the query did not work, could be related to the notice above.
--------------------------
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
--------------------------
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php
just mean the query did not work, could be related to the notice above.
ASKER
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
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.
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
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.
ASKER
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
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
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']))
if(isset($_POST['allrecord
if(isset($_POST['sort'])){
if(isset($_POST['ascdsc'])
$state = $_POST["state"];
$allrecords = $_POST["allrecords"];
$sort = $_POST['sort'];
$ascdsc = $_POST['ascdsc'];
with
if(isset($_POST['state']))
if(isset($_POST['allrecord
if(isset($_POST['sort'])){
if(isset($_POST['ascdsc'])
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\hill\query.php
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.
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.
ASKER
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);
?>
ASKER
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
The Query: SELECT lastName, firstName, areaCode, telephoneNumber FROM hill WHERE 'state' = 'Get State Records'ORDER BY lastname ASC
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'";
so your query should eb
SELECT lastName, firstName, areaCode, telephoneNumber FROM tbl telephone directory WHERE 'state' = '$state'";
ASKER
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
I apologize - and really appreciate your help.
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
I apologize - and really appreciate your help.
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
$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
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.
ASKER
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.
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);
?>
I need to see the form you are using, obviously it is not passsing the right information to the query.
ASKER
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>
this is what we are looking for from the form
if(isset($_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.
if(isset($_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.
ASKER