| s0l1dsnak3123 |
06-15-2008 12:10 PM |
[PHP] [mySQL] Finding all records with with a certain field.
hey there :)
I am having some problems with a PHP script I am developing.
The aim of the portion of the script I'm having problems with is to connect to a mySQL database, find a list of tables to search through, and then search for a piece of text under the field title of "Peripheral Type", with all the tables.
this is the portion I suspect is causing the problems:
PHP Code:
if ($_POST["product_type"]){ #search for product type, regardless of distro
$sql = @mysql_connect ($database_server, $database_user, $database_pass)
or die ($sql_conn_err_msg); #Connect to SQL Database
if (!@mysql_select_db($database_prefix . $database_name, $sql)) { // leave the database
echo $sql_conn_err_msg;
}
$result = mysql_query("SELECT * FROM dist_list", $sql); #find all distros
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
if ($distro == ""){ #make $distro a string with all the distros, seperated by whitespaces
$distro = $row['Distro'];
} else {
$distro = $distro . "####" . $row['Distro'];
}
}
$distro = explode("####",$distro);
mysql_free_result($result);
$per_type = $_POST["product_type"]; #get product type
if (!@mysql_select_db($database_prefix . $database_name, $sql)) { // leave the database
echo $sql_conn_err_msg;
}
echo "<table border=\"1\" color=\"black\">"; #create the start of the table
echo "<tr>";
echo "<td><strong>Distribution</strong></td>";
echo "<td><strong>Peripheral Type</strong></td>";
echo "<td><strong>Peripheral Name</strong></td>";
echo "<td><strong>Bugs/Limitations</strong></td>";
echo "<td><strong>How to install</strong></td>";
echo "<td><strong>Overall Happiness</strong></td>";
echo "<td><strong>Stars</strong></td>";
echo "<td><strong>Date</strong></td>";
echo "</tr>";
$i=0;
foreach ($distro as $name){ #for each distro in the list
$result = mysql_query("SELECT " . $per_type . " FROM " . $distro[$i], $sql); #query the product type on that record of distro
if (!$result) {
echo $sql_conn_err_msg;
exit;
}
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr>";
echo("<td> " . $distro[$i] . "</td>"); #print the distro
echo("<td> " . $row["Peripheral Type"] . "</td>");
echo("<td> " . $row["Peripheral Name"] . "</td>");
echo("<td> " . $row["Bugs/Limitations"] . "</td>");
echo("<td> " . $row["How to install"] . "</td>");
echo("<td> " . $row["Overall Happiness"] . "</td>");
if ($row["stars"] == 1){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 2){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"/portal/Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 3){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 4){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 5){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
}
echo("<td> " . $row["Date"] . "</td>");
echo "</tr>";
}
$i++;
}
$i=0;
echo "</table>";
mysql_free_result($result);
}
for good measure, here is the source for this script:
PHP Code:
<?php
/* Allows you to view various aspects of the database */
include 'config.php'; #include the config file
if ($_POST["distro"]){
$sql = @mysql_connect ($database_server, $database_user, $database_pass)
or die ($sql_conn_err_msg); #Connect to SQL Database
$distro_name = $_POST["distro"];
if (!@mysql_select_db($database_prefix . $database_name, $sql)) { // leave the database
echo $sql_conn_err_msg;
}
$result = mysql_query("SELECT * FROM " . $distro_name, $sql);
if (!$result) {
echo $sql_conn_err_msg;
exit;
}
echo "<table border=\"1\" color=\"black\">";
echo "<tr>";
echo "<td><strong>Distribution</strong></td>";
echo "<td><strong>Peripheral Type</strong></td>";
echo "<td><strong>Peripheral Name</strong></td>";
echo "<td><strong>Bugs/Limitations</strong></td>";
echo "<td><strong>How to install</strong></td>";
echo "<td><strong>Overall Happiness</strong></td>";
echo "<td><strong>Stars</strong></td>";
echo "<td><strong>Date</strong></td>";
echo "</tr>";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr>";
echo("<td> " . $distro_name . "</td>");
echo("<td> " . $row["Peripheral Type"] . "</td>");
echo("<td> " . $row["Peripheral Name"] . "</td>");
echo("<td> " . $row["Bugs/Limitations"] . "</td>");
echo("<td> " . $row["How to install"] . "</td>");
echo("<td> " . $row["Overall Happiness"] . "</td>");
if ($row["stars"] == 1){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 2){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"/portal/Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 3){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 4){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 5){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
}
echo("<td> " . $row["Date"] . "</td>");
echo "</tr>";
}
echo "</table>";
mysql_free_result($result);
mysql_close ($sql); #Close Connection with SQL Database
}
if ($_POST["product_type"]){ #search for product type, regardless of distro
$sql = @mysql_connect ($database_server, $database_user, $database_pass)
or die ($sql_conn_err_msg); #Connect to SQL Database
if (!@mysql_select_db($database_prefix . $database_name, $sql)) { // leave the database
echo $sql_conn_err_msg;
}
$result = mysql_query("SELECT * FROM dist_list", $sql); #find all distros
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
if ($distro == ""){ #make $distro a string with all the distros, seperated by whitespaces
$distro = $row['Distro'];
} else {
$distro = $distro . "####" . $row['Distro'];
}
}
$distro = explode("####",$distro);
mysql_free_result($result);
$per_type = $_POST["product_type"]; #get product type
if (!@mysql_select_db($database_prefix . $database_name, $sql)) { // leave the database
echo $sql_conn_err_msg;
}
echo "<table border=\"1\" color=\"black\">"; #create the start of the table
echo "<tr>";
echo "<td><strong>Distribution</strong></td>";
echo "<td><strong>Peripheral Type</strong></td>";
echo "<td><strong>Peripheral Name</strong></td>";
echo "<td><strong>Bugs/Limitations</strong></td>";
echo "<td><strong>How to install</strong></td>";
echo "<td><strong>Overall Happiness</strong></td>";
echo "<td><strong>Stars</strong></td>";
echo "<td><strong>Date</strong></td>";
echo "</tr>";
$i=0;
foreach ($distro as $name){ #for each distro in the list
$result = mysql_query("SELECT " . $per_type . " FROM " . $distro[$i], $sql); #query the product type on that record of distro
if (!$result) {
echo $sql_conn_err_msg;
exit;
}
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr>";
echo("<td> " . $distro[$i] . "</td>"); #print the distro
echo("<td> " . $row["Peripheral Type"] . "</td>");
echo("<td> " . $row["Peripheral Name"] . "</td>");
echo("<td> " . $row["Bugs/Limitations"] . "</td>");
echo("<td> " . $row["How to install"] . "</td>");
echo("<td> " . $row["Overall Happiness"] . "</td>");
if ($row["stars"] == 1){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 2){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"/portal/Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 3){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 4){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
} elseif ($row["stars"] == 5){
echo("<td>");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo("<img src=\"Images/star.png\">");
echo ("</td>");
}
echo("<td> " . $row["Date"] . "</td>");
echo "</tr>";
}
$i++;
}
$i=0;
echo "</table>";
mysql_free_result($result);
}
if ($_POST["product_name"]){
}
if ($_POST["product_stars"]){
}
if ($_POST["product_date"]){
}
?>
Please help me as I really cannot tell what I am doing wrong.
Thanks in advance,
s0l1dsnak3123 |
