Page 1 of 1
query for two tables
Posted: Wed Aug 15, 2007 10:11 am
by noob#10
i can't find a room that's discussing about sql queries but please, i need help...
i want to display 2 data from 2 tables..
eg:
tbl_category
cat_name
printer
----------------------------------------------
tbl_product
pd_name
HP
if i want to search it in a search module i want it to look like this:
CATEGORY
printer
PRODUCT
HP
here is my query:
$sql = "SELECT tbl_category.cat_name, tbl_product.pd_name FROM tbl_category, tbl_product WHERE tbl_category.cat_id = tbl_product.pd_id GROUP BY tbl_category.cat_name"
i'm also using .php in my modules.
hope that you enlighten me..
Posted: Wed Aug 15, 2007 10:49 am
by CoderGoblin
Not sure what you are really asking for
Table Category should have an indexed column say category_id.
Table Product should have a column referencing this column (also possibly called category_id although some people prefer something like category_ref).
When you want to join tables the sql is like
Code: Select all
SELECT product_name,category_name FROM category,product WHERE category.category_id=product.category_id GROUP BY category_name;
Posted: Wed Aug 15, 2007 11:12 am
by noob#10
sir,
this is the code, [s]pls[/s]
please prompt me if the structure is a mess..
Code: Select all
<html>
<head>
<body>
<font face='tahoma'>
<?php
if (!$_REQUEST['Submit'])
{
html_form();
}
else
{
select_product();
}
function html_form()
{
?>
<form name="form1" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
<b>Keyword Search:</b>   <input type="text" name="pd_name">  
<input type="submit" name="Submit" value="Display" />
</form>
<?
}
//--------------------------------------------------------------------------
function select_product()
{
/* set's the variables for MySQL connection */
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'thesis1';
$table = "tbl_category";
$link = @mysql_connect ($dbHost, $dbUser, $dbPass) or die (mysql_error());
@mysql_select_db("$dbName") or die("Unable to select database $dbName");
/* Defines the Active Database for the Connection */
$product = $_REQUEST['pd_name'];
/* Sets the SQL Query */
$sql = "SELECT pd_name,cat_name FROM tbl_category,tbl_product WHERE tbl_category.cat_id=tbl_product.cat_id GROUP BY cat_name;";
/* Passes a Query to the Active Database */
$result = mysql_query($sql, $link);
if (!$result)
{
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
else
$rows = mysql_num_rows($result);
/* Starts the table and creates headings */
?>
<table width='75%' border='0' border align='center' cellpadding='5' align='center'>
<tr>
<td colspan=3><font size=2 face='tahoma' color='red'>
Search for "<i><?=$product; ?></i>" Returned <b><?=$rows; ?></b> Result(s)
</td>
</tr>
<tr>
<th></th>
<th><font face='tahoma' size='2'>Product</th>
<th><font face='tahoma' size='2'>Category</th>
</tr>
<?
/* Retrieves the rows from the query result set and puts them into a HTML table row */
$ctr=0;
if ($rows > 0)
{
/* Displays HTML Form */
html_form();
?>
<p> </p>
<hr>
<br>
<?
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$ctr++;
$row = mysql_fetch_array($result, MYSQL_ASSOC);
?>
<tr>
<td width='10%' align = 'center'><font face='tahoma' size='2'><?=$ctr;?></td>
<td align = 'center'><font face='tahoma' size='2'><?=$row['pd_name'];?></td>
<td align = 'center'><font face='tahoma' size='2'><?=$row['cat_name'];?></td>
</tr>
<?
}
// if no records present
// display message
}
else
{
html_form();
?>
<p> </p>
<hr>
<?
?>
<tr>
<td colspan='3' align='center'><font face='tahoma' size="2" color='red'>
<strong><em>Query Matched No Record...</em></strong></font></td>
</tr>
<br>
<?
}
/* Closes the table */
?>
</font>
</table>
<br>
<?
}
?>
<hr>
<p><a href="mainmenu.php">Return to Main Menu</a></p>
</font>
</body>
</html>
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.
Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.
Posted: Thu Aug 16, 2007 4:39 am
by CoderGoblin
Personal Style I would use...
Code: Select all
<?php
/**
** Define all variables (my preference)/functions at the top of the page
**/
$form="";
$table="";
function selectProduct($product)
{
// I like to define variables needed in the function here
$error='';
$result_counter=1;
// Note we pass in parameter $product here so we could potentially use this function without the form.
$dbHost = 'localhost';
$dbUser = 'root'; // !!!! NEVER USE ROOT CREATE A WEB USER
$dbPass = ''; // !!!! ALWAYS HAVE PASSWORD
$dbName = 'thesis1';
$table = "tbl_category";
$link = @mysql_connect ($dbHost, $dbUser, $dbPass) or die (mysql_error());
$product_sql = mysql_escape_string($product); // !! Always escape strings -- Never trust user input
// Not sure if you are actually after the product as part of the selection but I guess so. We use UPPER to ensure case doesn't matter.
// Remove the upper if not advantagous as it will slow the select down.
$sql = "SELECT pd_name,cat_name
FROM tbl_category,tbl_product
WHERE tbl_category.cat_id=tbl_product.cat_id AND
UPPER(pd_name) LIKE UPPER('%{$product_sql}%')
GROUP BY cat_name";
$result = mysql_query($sql, $link);
// Have we made a booboo with the sql ?
if (!$result) {
$found=0;
$error="<p>Error performing query: ".mysql_error()."</p>";
} else {
// Check we have results
$found=mysql_num_rows($result);
if ($found == 0) $error="<p>No results found</p>";
}
// Define the top of the table including headers
$output = <<<TABLETOP
<table width='75%' border='0' border align='center' cellpadding='5' align='center'>
<tr>
<td colspan=3><font size=2 face='tahoma' color='red'>
Search for "<i>{$product;}</i>" Returned <b>{$found}</b> Result(s)
</td>
</tr>
<tr>
<th></th>
<th><font face='tahoma' size='2'>Product</font></th>
<th><font face='tahoma' size='2'>Category</font></th>
</tr>
TABLETOP;
if ($found > 0) {
// Add each row to the table
while ($row=mysql_fetch_assoc($result)) {
$result_counter++;
$output.= <<<TABLEBLOCK
<tr>
<td width='10%' align='center'><font face='tahoma' size='2'>{$result_counter}</td>
<td><font face='tahoma' size='2'>{$row['pd_name']}</font></th>
<td><font face='tahoma' size='2'>{$row['cat_name']}</font></th>
</tr>
TABLEBLOCK;
}
} else {
/// No results found so output the fact
$output.='<tr><td colspan="3">'.$error.'</td></tr>';
}
// Close the table
return $output.="</table>";
}
/*************************************************************************************************
*
* Process Block
*
*************************************************************************************************/
// Check if form result exists
if (!empty($_POST['Submit'])) {
$table=selectProduct($_POST['pd_name');
}
// Define the form to output
$form= <<< EOFORM
<form name="form1" method="post">
<label for="pd_name"><b>Keyword Search:</b></label> 
<input id="pd_name" type="text" name="pd_name" />  
<input type="submit" name="Submit" value="Display" />
</form>
EOFORM;
?>
<html>
<head>
</head>
<body>
<?php echo $form; ?>
<?php echo $table; ?>
<hr />
<p><a href="mainmenu.php">Return to Main Menu</a></p>
</body>
</html>
I realise the result is probably different to yours... Haven't got the time to make an exact match... but see what you think as far as coding style goes.
Also learn to use CSS rather than <font> tags.
One reason for coding style change... If you need to redirect within the (!$_REQUEST['Submit']) block you can't in the original cose as you have already output something. This is a very common mistake.
[Edit] fixed a missing quote and ;
query for two tables [SOLVED]
Posted: Thu Aug 16, 2007 6:55 am
by noob#10
thank you sir for the help! really appreciated it.. hope to hear from [s]u[/s]
you soon..
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.
Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.
Posted: Thu Aug 16, 2007 7:06 am
by CoderGoblin
You're welcome, we are here to try to help.. We all need it sometimes. As I have previously mentioned this is MY preference. Others may disagree but if you want to know why I do things in a particular way just ask.