Hi! I need help with the following code. This code gives the results in a single row. I need it to be displayed in a selectbox. and when an option is selected (only one option, no multiple options), it is displayed on a single column table on the page. I need expert help with this. please. thanks.
<?php
$host = 'localhost';
$port = '3306';
$server = $host . ':' . $port;
$user = 'root';
$password = '';
$link = count($t_tmp = explode(':', $server)) > 1 ? mysqli_connect($t_tmp[0], $user, $password, '', $t_tmp[1]) : mysqli_connect($server, $user, $password);
if (!$link) {
die('Error: Could not connect: ' . mysqli_error($link));
}
$database = 'mydb';
mysqli_select_db($link, $database);
$query = 'select * from mytable';
$result = mysqli_query($link, $query);
if (!$result) {
$message = 'ERROR:' . mysqli_error($link);
return $message;
} else {
$i = 0;
echo '<html><body><table><tr>';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<td>' . $meta->name . '</td>';
$i = $i + 1;
}
echo '</tr>';
echo '</table></body></html>';
mysqli_free_result($result);
}
mysqli_close($link);
?>
So make sure it is inside a tag and use the following:
echo '<select name="mySelect" id="mySelect">';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<option>' . $meta->name . '</option>';
$i = $i + 1;
}
echo '</select>';
1 Like
thank you, SpikeZ. It is perfect. I did what you said, and here it is working. if it is not too much to ask, how would the selected value from the dropdown list display the contents ? is it possible that with one single code to get field names display their content one by one as selected in the dropdown box?
<?php
$host = 'localhost';
$port = '3306';
$server = $host . ':' . $port;
$user = 'root';
$password = '';
$link = count($t_tmp = explode(':', $server)) > 1 ? mysqli_connect($t_tmp[0], $user, $password, '', $t_tmp[1]) : mysqli_connect($server, $user, $password);
if (!$link) {
die('Error: Could not connect: ' . mysqli_error($link));
}
$database = 'mydb';
mysqli_select_db($link, $database);
$query = 'select * from mytable';
$result = mysqli_query($link, $query);
if (!$result) {
$message = 'ERROR:' . mysqli_error($link);
return $message;
} else {
$i = 0;
echo '<select name="mySelect" id="mySelect">';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<option>' . $meta->name . '</option>';
$i = $i + 1;
}
echo '</select>';
}
mysqli_close($link);
?>
Glad it works so far!
Do you mean when the user selects the OPTION it submits the form and displays the results from another database query on the page?
yes, when the user selects an option from the dropdown list (the options are the field (column) names in the mysql table), so the data in that field are displayed on the page. All are in the same database and same table.
Hi SpikeZ, can you help me with the second part too? please.
Hi merip33
You need to look at a few different building blocks to achieve what you need.
<?php
/*
This part should be in a separate file and included once!
*/
$host = 'localhost';
$port = '3306';
$server = $host . ':' . $port;
$user = 'root';
$password = '';
$link = count($t_tmp = explode(':', $server)) > 1 ? mysqli_connect($t_tmp[0], $user, $password, '', $t_tmp[1]) : mysqli_connect($server, $user, $password);
if (!$link) {
die('Error: Could not connect: ' . mysqli_error($link));
}
$database = 'mydb';
mysqli_select_db($link, $database);
/* ---- */
$query = 'select * from mytable';
$result = mysqli_query($link, $query);
if (!$result) {
$message = 'ERROR:' . mysqli_error($link);
return $message;
} else {
$i = 0;
echo '<form name="selectSomething" action="" method="GET">';
echo '<select name="mySelect" id="mySelect" onchange="this.form.submit()">';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<option>' . $meta->name . '</option>';
$i = $i + 1;
}
echo '</select>';
echo '</form>';
}
// only run this part IF the form has been submitted
if(isset($_GET['mySelect'])) {
// in here, run a query against the table and column that holds the data you are looking to display
}
mysqli_close($link);
Quick and dirty but should give you a starting point!
is it possible to run a single query that will apply to each of the field names? Instead of writing different queries for every field name, can I do this with a single query? because when I say $sql = âSELECT fieldname1 FROM mytableâ, I have to this for every fieldname?
What I did is this: ( I separated the first part, and put it in a config file)
config.php
<?php
/*
This part should be in a separate file and included once!
*/
$host = 'localhost';
$port = '3306';
$server = $host . ':' . $port;
$user = 'root';
$password = '';
$link = count($t_tmp = explode(':', $server)) > 1 ? mysqli_connect($t_tmp[0], $user, $password, '', $t_tmp[1]) : mysqli_connect($server, $user, $password);
if (!$link) {
die('Error: Could not connect: ' . mysqli_error($link));
}
$database = 'mydb';
mysqli_select_db($link, $database);
?>
and the rest in the second file. main.php:
<?php
include ("config.php");
$query = 'select * from myTable';
$result = mysqli_query($link, $query);
if (!$result) {
$message = 'ERROR:' . mysqli_error($link);
return $message;
} else {
$i = 0;
echo '<form name="selectSomething" action="" method="GET">';
echo '<select name="mySelect" id="mySelect" onchange="this.form.submit()">';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<option>' . $meta->name . '</option>';
$i = $i + 1;
}
echo '</select>';
echo '</form>';
}
// only run this part IF the form has been submitted
if(isset($_GET['mySelect'])) {
<table class="tbresult">
$sql = "SELECT 'mySelect' FROM myTable";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc())
{
echo "<tr><td>" . $row["mySelect"] . "</td></tr>";
}
echo "</table>";
} else { echo "0 results"; }
$conn->close();
?>
</table>
}
mysqli_close($link);
?>
Here I added a query which I hoped itâd work, but it didnât.
$sql = "SELECT 'mySelect' FROM myTable";
Did you intend to include âmySelectâ (the string) in that query, or were you intending to use the contents of your $_GET variable?
My intention was to use the $_GET variable so that I can fetch the contents of mySelect. I am not sure if something like this is possible though.
It is, but you just used the string âmySelectâ rather than adding the value of the variable called $_GET['myselect']. You check that the variable exists a couple of lines earlier, but then donât use it.
ok, I did this, but I got the following error when I made a selection in the dropdown list.
Notice: Undefined variable: conn in C:\wamp64\www\main.php on line 32
Fatal error: Call to a member function query() on null in C:\wamp64\www\main.php on line 32.
and the line 32 is this : $result = $conn->query($sql);
<table class="tbresult">
<?php
include ("config.php");
$query = 'select * from myTable';
$result = mysqli_query($link, $query);
if (!$result) {
$message = 'ERROR:' . mysqli_error($link);
return $message;
} else {
$i = 0;
echo '<form name="selectSomething" action="" method="GET">';
echo '<select name="mySelect" id="mySelect" onchange="this.form.submit()">';
while ($i < mysqli_field_count($link)) {
$meta =
mysqli_fetch_field_direct($result, $i);
echo '<option>' . $meta->name . '</option>';
$i = $i + 1;
}
echo '</select>';
echo '</form>';
}
// only run this part IF the form has been submitted
if(isset($_GET['mySelect'])) {
$sql = "SELECT 'mySelect' FROM myTable";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc())
{
echo "<tr><td>" . $row[$_GET['myselect']] . "</td></tr>";
}
echo "</table>";
} else { echo "0 results"; }
$conn->close();
}
mysqli_close($link);
?>
Why have you suddenly changed the way that you execute queries? At the top, you just call mysqli_query() to run it, and now youâve changed to an OO method. You should be consistent. Where do you define $conn? Is it in your included file? If it is not, thatâs your problem. But you can get around that by just calling the query the way you do at the start of the code.
You havenât addressed this problem, though. Youâre still not using the selected variable in your query.
sorry for the late reply. I was away. I just have some basic PHP knowledge, Iâm not familiar with these methods. I had to make some changes because I couldnât run the code otherwise.
About sql query, I tried this: $sql = âSELECT â$_GET[âmyselectâ]â FROM myTableâ; but it doesnât work either.
Itâs not going to work if you donât use your own column. If you use the exact column as the example, it will not work if you donât have the same exact column in your database. Also, I wouldnât recommend using the provided snippet. It has potential vulnerabilities and are dangerous.
Have a read up on string concatenation, itâs quite straightforward when you get used to it. The issue you have with what you typed there is maybe something to do with the quotes.
For the most useful help, you need to steer clear of typing this any time, ever. What didnât work? Does it create the $sql string correctly but the query doesnât work, or is the query string incorrect (hint: echo or var_dump() the string to see if the query looks valid). Did you get an error message, if so what was it, and at what line?
Get used to working through your code, line by line, until you can narrow down exactly where an error occurs. Youâll see how itâs all working and get much better at spotting problems yourself that way.
What you mean with your own column? Iâm sorry but I didnât understand it. What I try to achieve here actually and technically should work with all tables.
What I mean by your own column is that this is just a sample code. If you are using the exact columns from this sample code and your columns are entirely different from this sample codeâs column, then you need to use your own columns. It will not simply âworkâ if your columns in your database is different from the sample code and you use the same code without changing it to your own columns.