How to pass value of one mysql query to the next query on the same page?


#1

Hi,
I have php code dd.php retrieves the mysql tables columns in two drop down, when selected from first drop down category from first table category it reloads and retrieves only the related entries from the second table subcategory and column subcategory (same like country and state drop down) when i submit the button the dd-check.php prints the values of the two drop down list, here is output:
Value of $cat = 2
Value of $subcat = user@gmx.ie
i want to add a third select query to get the $subcat variable as condition and print the all row, how to pass the value of $subcat in the same page to the query3, this is the dd.php code:
<?php
require ‘config.php’; // Database connection
//////// End of connecting to database ////////
?>

<!doctype html public “-//w3c//dtd html 3.2//en”>

Multiple drop down list box <?Php

@$cat=$_GET[‘cat’]; // Use this line or below line if register_global is off
if(strlen($cat) > 0 and !is_numeric($cat)){ // to check if $cat is numeric data or not.
echo “Data Error”;
exit;
}

///////// Getting the data from Mysql table for first list box//////////
$quer2=“SELECT DISTINCT category,cat_id FROM category order by category”;
///////////// End of query for first list box////////////

/////// for second drop down list we will check if category is selected else we will display all the subcategory/////
if(isset($cat) and strlen($cat) > 0){
$quer=“SELECT DISTINCT subcategory FROM subcategory where cat_id=$cat order by subcategory”;
}else{$quer=“SELECT DISTINCT subcategory FROM subcategory order by subcategory”; }
////////// end of query for second subcategory drop down list box ///////////////////////////

echo “”;
/// Add your form processing page address to action in above line. Example action=dd-check.php////
////////// Starting of first drop downlist /////////
echo “<select name=‘cat’ onchange=“reload(this.form)”>Select one”;
foreach ($dbo->query($quer2) as $noticia2) {
if($noticia2[‘cat_id’]==@$cat){echo “$noticia2[category]”."
";}
else{echo “$noticia2[category]”;}
}
echo “”;
////////////////// This will end the first drop down list ///////////

////////// Starting of second drop downlist /////////
echo “Select one”;
foreach ($dbo->query($quer) as $noticia) {
echo “$noticia[subcategory]”;
}
echo “”;
////This is my third query3 ////
$subcat=$_GET[‘subcat’];
$query3 = $dbo->prepare(“SELECT * FROM subcategory WHERE subcategory=?”);
$query3->execute([$subcat]);
while ($row = $query3->fetch(PDO::FETCH_ASSOC))
{
$cat = $row[‘cat_id’];
$name = $row[‘name’];
$from = $row[‘subcategory’];
$user = $row[‘user’];
$passw= $row[‘passw’];
$server = $row[‘server’];
$auth = $row[‘auth’];
$secure = $row[‘secure’];
$port = $row[‘port’];
}

////////////////// This will end the second drop down list ///////////
//// Add your other form fields as needed here/////
echo “”;
echo “”;
?>



Reset and start again


The problem is query3 cannot get the $subcate from the query above?

#2

Well, it looks like you are missing the query for “quer” just before the query for “quer2”.
I do not see the $PDO->query($quer); anywhere…


#3

You need fix your database before you do anything. There is no such thing as a sub category in DB design. EVERYTHING is a category. Some are parents, some are children.

Here are a couple links to get you going on the right track.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

http://www.mysqltutorial.org/mysql-adjacency-list-tree/


#4

These are tables and subcategory structure:
Database changed
mysql> show tables;
±---------------+
| Tables_in_mail |
±---------------+
| category |
| subcategory |
±---------------+
2 rows in set (0.00 sec)
mysql> DESCRIBE subcategory;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| cat_id | int(2) | NO | | 0 | |
| name | varchar(30) | YES | | NULL | |
| subcategory | varchar(50) | NO | | | |
| user | varchar(50) | NO | | | |
| passw | varchar(50) | NO | | | |
| server | varchar(50) | NO | | | |
| auth | varchar(10) | YES | | NULL | |
| secure | varchar(10) | YES | | NULL | |
| port | int(3) | YES | | NULL | |
±------------±------------±-----±----±--------±------+
9 rows in set (0.01 sec)


#5

An RDMS is not a spreadsheet. Learn “Database Normalization” and refer to the previous post for Categories. Until the DB is correct there is no point even thinking about code.