Jump to content

php mysql table joining

rinteractive

By rinteractive
in PHP Coding Help

 Share

Recommended Posts

rinteractive Newbie

rinteractive

Posted
Posted

How to display particular column from joining two table

 

 

I have two table category and head

Categoty has two field id , name. head table has id,name,cat

 

This page display the category list (cat-list.php)

<?php

include ("../include/config.php");

 

$sql = "SELECT  id, name FROM  category";

$results = mysql_query($sql) or die ("Err, Problem with the database....".mysql_error());

$num=1;

while ($row = mysql_fetch_row($results)){

$id = $row[0];

$name = $row[1];

 

 

 

$display_str .= "

<tr bgcolor=\"#FFFFFF\">

<td align=\"center\">$num</td> <td align=\"center\"><input type=\"radio\" name=\"id\" value=\"$id\"></td>

<td align=\"center\"><a href=\"head-list.php?cat=$id&from=$name\">$name</td>

 

</tr>";

$num++;

}

 

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Anti Corruption Intelligence Committee (ACIC) Admin Home</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" href="css/main.css" type="text/css" />

<script language="javascript" type="text/javascript">

<!--

function validate(action_type){

is_radio_checked=false;

if (document.cats.id.length){

no_of_radio=document.cats.id.length;

for (i=0;i < no_of_radio;i++){

if (document.cats.id.checked == true){

is_radio_checked=true;

break;

}

}

}

else{

if (document.cats.id.checked) is_radio_checked=true;;

}

 

if (is_radio_checked == false){

alert("Please Select the Type")

return false;

}

 

document.cats.action_type.value = action_type;

 

if (action_type == "edit"){

document.cats.submit();

return true;

}

if (action_type == "delete"){

delete_confirm = confirm("IMPORTANT: Are you sure you want to delete? ALL the products inside this Family will be DELETED.");

if(delete_confirm){

document.cats.submit();

return true;

}

else{

return false;

}

}

return false;

}

-->

</script>

</head>

 

<body>

<strong><h2 align="center">Select a <u>Category</u> to edit</h2></strong>

<?php

if ($msg == '1'){

?>

<center><big><font color="green"><strong>Your Changes Have Been Saved !!!</strong></font></big></center>

<?php

}

if ($msg == '2'){

?>

<center><big><font color="green"><strong>Records Successfully Deleted !!!</strong></font></big></center>

<?php

}

?>

 

<form name="cats" action="cat-action.php" method="post">

<table width="50%" align="center" cellpadding="2" cellspacing="1" class="TableBG" border="2">

<tr bgcolor="#FFFFFF">

<th class="BlueBG"><strong>#</strong></th>

<th class="BlueBG"><strong>Select</strong></th>

<th class="BlueBG"><strong>Category</strong></th>

 

</tr>

<?php echo $display_str; ?>

<tr bgcolor="#FFFFFF">

<td align="center" colspan="5">

<input type="button" name="edit" value="Edit Category" class="FormButton" onclick=validate("edit")>

   

<input type="button" name="delete" value="Delete Category" class="FormButton" onclick=validate("delete")>

<input type="hidden" value="" name="action_type">

</td>

</tr>

<tr bgcolor="#FFFFFF">

<td align="center" colspan="5" height="30"><a href="cat-add.php"><b>Add Category</b></a></td>

</tr>

</table>

</form>

</body>

</html>

 

Below page should display head units which belongs to the particular that is when I click on particular category it should display head units which belongs to that  category.

 

<?php

include ("../include/config.php");

include_once("../include/functions.php");

 

 

$cat = cleanvar(h_($_REQUEST['cat']));

 

$sql = "SELECT name FROM category WHERE id = '".d_($cat)."'";

//echo $sql;

$cat_res = mysql_query($sql) or die ("Err, Problem with the database....".mysql_error());

if(mysql_num_rows($cat_res) > 0){

$cat_name = mysql_result($cat_res,0,0);

}

 

 

 

$sql = "SELECT id,name FROM head WHERE cat = '".d_($cat)."'";

//echo $sql;

$results = mysql_query($sql) or die ("Err, Problem with the database....".mysql_error());

$num=1;

while ($row = mysql_fetch_row($results)){

$prod_id = $row[0];

$name = $row[1];

 

 

$show_cats .= "

<tr bgcolor=\"#FFFFFF\">

<td align=\"center\">$num</td>

<td align=\"center\"><input type=\"radio\" name=\"prod_id\" value=\"$prod_id\"></td>

<td align=\"center\">$name</td>

 

</tr>";

$num++;

}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Anti Corruption Intelligence Committee (ACIC) Admin Home</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" href="css/main.css" type="text/css" />

<script language="javascript" type="text/javascript">

<!--

function validate(action_type){

is_radio_checked=false;

if (document.headlist.id.length){

no_of_radio=document.cats.id.length;

for (i=0;i < no_of_radio;i++){

if (document.headlist.id.checked == true){

is_radio_checked=true;

break;

}

}

}

else{

if (document.headlist.id.checked) is_radio_checked=true;;

}

 

if (is_radio_checked == false){

alert("Please Select the Type")

return false;

}

 

document.headlist.action_type.value = action_type;

 

if (action_type == "edit"){

document.headlist.submit();

return true;

}

if (action_type == "delete"){

delete_confirm = confirm("IMPORTANT: Are you sure you want to delete? ALL the products inside this Family will be DELETED.");

if(delete_confirm){

document.headlist.submit();

return true;

}

else{

return false;

}

}

return false;

}

-->

</script>

</head>

 

<body>

<strong>

<h2 align="center">Select Head unit to edit</h2>

</strong>

<?php

if ($msg == '1'){

?>

<center><big><font color="green"><strong>Your Changes Have Been Saved !!!</strong></font></big></center>

<?php

}

if ($msg == '2'){

?>

<center><big><font color="green"><strong>Records Successfully Deleted !!!</strong></font></big></center>

<?php

}

?>

 

<form name="headlist" action="head-list-action.php" method="post">

<table width="50%" align="center" cellpadding="2" cellspacing="1" class="TableBG" border="2">

<tr bgcolor="#FFFFFF">

<th class="BlueBG"><strong>#</strong></th>

<th class="BlueBG"><strong>Select</strong></th>

<th class="BlueBG"><strong>Head unit</strong></th>

 

</tr>

<?php echo $display_str; ?>

<tr bgcolor="#FFFFFF">

<td align="center" colspan="5"><input type="button" name="edit" value="Edit Head Unit" class="FormButton" onClick=validate("edit")>      

<input type="button" name="delete" value="Delete Head Unit" class="FormButton" onclick=validate("delete")>

<input type="hidden" value="" name="action_type">   </td>

</tr>

<tr bgcolor="#FFFFFF">

<td align="center" colspan="5" height="30"><a href="head-add.php"><b>Add Head Unit</b></a></td>

</tr>

</table>

</form>

</body>

</html>

the output wht i need i am not getting

 

Hai Guys!!!!

if any body know the coding please do the reply

Link to comment
Share on other sites
eZe616 Member

eZe616

Posted
Posted 

$sql = "SELECT  id, name FROM  category";

 

I would write this piece of code as "SELECT * FROM category", since you ARE selecting all the fields in the category table. It's less writing for once.

 

Category has two field id , name. head table has id,name,cat

 

Now, as I understand the tables, your using the 'cat' field to join the two tables together. It would be better to join them using the category id field. An Example, your head table could look like this:

Head-----
id int(3) NOT NULL
category_id int(2) NOT NULL
name varchar(32) NOT NULL

'

or something in that way.

 

and to join them together you can try - I haven't tested it in mySQl though - 

$sql = "SELECT C.name, H.name, H.id FROM Category C INNER JOIN Head H ON C.id = H.category_id

 

or something like that...not sure about it though..since I don't fully understand what you're trying to do

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.