How to retrieve correct number of records from 3 tables

[geekisthenewsexy]

hello, can someone help me out on how to retrieve the correct number of records from tables with indefinite number of records?

 

Say for example,

 

table1:

___________

c_id | c_name

1    | BSA

 

table2:

_____________

y_id | c_id | year

1      | 1    |First year

 

table3:

_____________

b_id | y_id | block

1      | 1    | BSA1-A

2      | 1    | BSA1-B

 

As you can see,there are 3 table with the first two having one row and last having two rows,two records. Now, i want to be able to retrieve C_NAME,YEAR and BLOCK and echo it for the user to see. The output should be:

____________________

c_name | year      | block

BSA      | Firs year | BSA1-A

                              | BSA1-B

 

But what I got was:

___________________

c_name | year      | block

BSA      | First year | BSA1-A

BSA      | First year | BSA1-B

 

How will I able to do this?I've used SELECT DISTINCT,and even joined the tables thinking that would fix it but it still outputs the same result..pls. help.. :-\

[chintansshah]

Use the same query which you have written. After getting result use club array function.

 

function club_array($contents,$parent_id)

{

if(count($contents)>0)

{

$list = array();

$children = array();

foreach ($contents as $v )

{

$pt = $v[$parent_id];

@$list = $children[$pt] ? $children[$pt] : array();

array_push( $list, $v );

$children[$pt] = $list;

}

$contents = $children;

 

}

return $contents;

}

 

 

[geekisthenewsexy]

uhm..what does club array function do? :-\

[kickstart]

Hi

 

The output you want can't really come directly from mysql. Mysql is returning rows of data, but you want some of that data not to be returned if the previous row was the same which sql isn't really set up to do.

 

Basically what you need to do is process it with php and only put out the first column when the value changes.

 

All the best

 

Keith

[geekisthenewsexy]

ok, i see.. uhm,do you have like an example for that?so i may be able to adapt it in my code?

[chintansshah]

It will give an output like

 

[0] =>array()

{

    [id] => BSA

    [year] => 2010

    [block] =>array()

            {

              [0] => BSA1-A

              [1] => BSA1-B

              }

 

}

[kickstart]

ok, i see.. uhm,do you have like an example for that?so i may be able to adapt it in my code?

 

Hi

 

Something like this:-

 

<?php

$rs = mysql_query($sql) or die(mysql_error());

$prevName = '';
$prevYear = '';
$OutLine = array('OutName'=>'','OutYear'=>'','OutBlock'=>'');

while ($row = mysql_fetch_array($rs))
{
$OutLine['OutName'] = $row['c_name'];
$OutLine['OutYear'] = $row['year'];
$OutLine['OutBlock'] = $row['block'];
if ($prevName == $OutLine['OutName'])
{
	$OutLine['OutName'] = '';
	$OutLine['OutYear'] = '';
}
else
{
	$prevName = $OutLine['OutName'];
	if ($prevYear == $OutLine['OutYear'])
	{
		$OutLine['OutYear'] = '';
	}
	else
	{
		$prevYear = $OutLine['OutYear'];
	}
}
echo implode('|',$OutLine)."<br />";
}

?>

 

Or a bit more compact but not as understandable:-

 

<?php

$rs = mysql_query($sql) or die(mysql_error());

$prevName = '';
$prevYear = '';

while ($row = mysql_fetch_array($rs))
{
echo (($row['c_name'] == $prevName) ? '' : $row['c_name']).'|'.(($row['year'] == $prevYear AND $row['c_name'] == $prevName)) ? '' : $row['year']).'|'.$row['block'].'<br />';
$prevName = $row['c_name'];
$prevYear = $row['year'];
}

?>

 

All the best

 

Keith

[geekisthenewsexy]

wow, ok i was able to try the code you gave me and it worked! thanks! 

 

there are some slight problems though. i entered another set of record, but when i viewed the records the year appeared to be missing on the next record. only on the first one. probably if it's inside a table form it'll work? i want also to be able to echo this into a table..here's the code:

 

echo "<table border='1' cellpadding='10'>";
echo "<tr> <th>Course</th> <th>Year</th> <th>Block</th><th></th> <th></th></tr>";

echo "<tr>";
echo '<td>' . $row['c_name'] . '</td>';
echo "</tr>"; 
echo '<td>' . $row['year'] . '</td>';
echo '<td>' . $row['block'] . '</td>';


echo '<td><a href="edit.php?course=' . $row['c_name'] . ';year='.$row['year'].';block=' . $row['block'] . '">Add room(s)</a></td>';
echo "</table>";

[kickstart]

Hi

 

Take it you used my first example. Just seen the error I made there.

 

<?php

$rs = mysql_query($sql) or die(mysql_error());

$prevName = '';
$prevYear = '';
$OutLine = array('OutName'=>'','OutYear'=>'','OutBlock'=>'');

while ($row = mysql_fetch_array($rs))
{
$OutLine['OutName'] = $row['c_name'];
$OutLine['OutYear'] = $row['year'];
$OutLine['OutBlock'] = $row['block'];
if ($prevName == $OutLine['OutName'])
{
	$OutLine['OutName'] = '';
	$prevName = $OutLine['OutName'];
	if ($prevYear == $OutLine['OutYear'])
	{
		$OutLine['OutYear'] = '';
	}
	else
	{
		$prevYear = $OutLine['OutYear'];
	}
}
echo implode('|',$OutLine)."<br />";
}

?>

 

Using the 2nd example and trying to do the table

 

<?php

$rs = mysql_query($sql) or die(mysql_error());

$prevName = '';
$prevYear = '';

while ($row = mysql_fetch_array($rs))
{
echo (($row['c_name'] == $prevName) ? '' : "<tr><td colspan='2'>".$row['c_name']).'</td></tr>';
    echo '<tr><td>'.(($row['year'] == $prevYear AND $row['c_name'] == $prevName)) ? '' : $row['year']).'</td><td>'.$row['block'].'</td></tr>';
$prevName = $row['c_name'];
$prevYear = $row['year'];
}

?>

 

(excuse any typos and the dodgy formatting)

 

All the best

 

Keith

[geekisthenewsexy]

hey,thank you so much for the help.

okay it's now working..and i have another problem.it seems that on the form, the duplicated values of c_name from the table are just hidden.

because it's like this situation:

 

ok, user fills up form then submit.

he entered:

c_name: BSA ,

year:1

block:.etc..

note that c_name is BSA,year is 1

 

but he wants to add another record. and entered BSA again on c_name,but this time year is 2,block:..etc..

note:c_name input is BSA again.

when form is submitted,on the database:

on table course:

c_id | c_name

1    | BSA

2    | BSA

 

table year

y_id | c_id | year

1    | 1    | 1

2    | 2    | 2

 

now, what i want is instead of two BSA, table course should only contain:

c_id | c_name

1    | BSA

 

so that table year will become:

y_id | c_id | year

1    | 1    | 1

2    | 1    | 2

 

is there a way for this not to duplicate?like using DELETE and UPDATE and sort of deleting the duplicate in the table course only and in the table year, c_id 2 to be replaced by one when the c_name entered is the same value?and this process should also be the same for future inputs..is this concerned only in the php codes?or mysql still involved?please help..:'(

 

and also having no duplicate c_name on table course is very important for the records here,on the column c_name will be fetched later on in my dynamic dropdown menu.. :'(

[geekisthenewsexy]

okay, i think what i should probably meant was how to merge/combine the duplicates

on table course:

c_id | c_name

1    | BSA

2    | BSA

 

to become:

c_id | c_name

1    | BSA

 

thus table year becomes:

y_id | c_id | year

1    | 1    | 1

2    | 1    | 2

 

i've looked for commands like REPLACE INTO but this is concerned with duplicate primary/unique ids. not duplicate values.. :'(

[kickstart]

Hi

 

Do you mean to eliminate the duplicates on the mysql table, or on the output data?

 

To eliminate on the table you need to check if a row already exists and insert one if not / get the key of one that already exists.

 

All the best

 

Keith

[geekisthenewsexy]

hi,

yes i mean the duplicates on the table but not actually deleting them or updating because that would mean replacing an old row with the new one..

To eliminate on the table you need to check if a row already exists and insert one if not / get the key of one that already exists.

 

uhm,so i'm going to use mysql_num_rows on this?like

if(mysql_num_rows(mysql_query("SELECT c_name FROM course WHERE c_name = $_POST[course]"))){
// Code inside if block if c_name is already there
} 

 

or is mysql_fetch_array better?and how about for getting the key of one that exists?the $_GET method involves?but i'm confused how to form that up. :-\

do you have an example? :-\

 

[geekisthenewsexy]

thanks kickstart for your overflowing patience! 

i'm really glad i joined this forum.

[kickstart]

Hi

 

You can use a SELECT and check for the existance, and then do an insert as required. This is easy to code and understand but is slow.

 

You need to fetch the array if one exists as you need the id of the existing record.

 

If you already have duplicates then you need to manually sort them out, find the duplicates, pick which one you want to keep, update any foreign keys that refer to the ones you are going to delete and then delete the unwanted ones.

 

All the best

 

Keith

[geekisthenewsexy]

hi there,

thanks for the advise. i'm working on it now. i know it's confusing but i kind of don't want to delete the duplicates..

maybe just overwrite the existing on the table

 

c_id | c_name

1    | BSA

2    | BSA

 

..?and also

You can use a SELECT and check for the existance, and then do an insert as required. This is easy to code and understand but is slow

 

so is there another way then?

[kickstart]

Hi

 

Depends on the situation, but often what you need is INSERT....ON DUPLICATE UPDATE...., but not sure that is usable for what you want here.

 

However what you can do (but not reliably using an innodb) is:-

 

INSERT INTO table1 (c_name) VALUES ("BSA")  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

 

This is a bit of a bodge, but triggers the return of the inserted or updated id in mysql_insert_id()

 

See the Mysql documentation for a bit more info

 

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 

To be honest it is not something I have had to use.

 

All the best

 

Keith

[geekisthenewsexy]

hi,

so okay,i've tried the code you gave me but it doesn't seem to work..(or maybe my coding just isn't right) :-\

here:

 

$result=mysql_query("SELECT c_id, c_name FROM course WHERE c_name=$_POST[course]")or die (mysql_error());
$row=mysql_fetch_array($result);
if($row>0){
$sql=mysql_query("INSERT INTO course (c_name) VALUES ('$_POST[course]') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(c_id)")or die (mysql_error());
$c_id= mysql_insert_id(); 
$sql=mysql_query("INSERT INTO year VALUES (NULL, $c_id, '$_POST[year]')") or die (mysql_error());
$yid = mysql_insert_id(); 
while(count($block)>$intblock)
{
if (($block[$intblock]<>"")){
$sql=mysql_query("INSERT INTO block VALUES (NULL, '$yid', '".$block[$intblock]."')") or die (mysql_error());
$block_id=mysql_insert_id(); 
mysql_query($sql);
}
else{
echo "Block ".($intblock+1)." is missing values and cannot be inserted.";
}
$intblock=($intblock+1);
}
else
{
$sql=mysql_query("INSERT INTO course VALUES (NULL,'$_POST[course]')")or die (mysql_error());
$c_id= mysql_insert_id(); 
$sql=mysql_query("INSERT INTO year VALUES (NULL, $c_id, '$_POST[year]')") or die (mysql_error());
$yid = mysql_insert_id(); 
while(count($block)>$intblock)
{
if (($block[$intblock]<>"")){
$sql=mysql_query("INSERT INTO block VALUES (NULL, '$yid', '".$block[$intblock]."')") or die (mysql_error());
$block_id=mysql_insert_id(); 
mysql_query($sql);
}
else{
echo "Block ".($intblock+1)." is missing values and cannot be inserted.";
}
$intblock=($intblock+1);
}

 

there..but i think i might have a solution. well sort of,if it works.

i was thinking maybe if i could get the last inserted record (last inserted row of c_id and c_name)from the table course right after it was being inserted, and sort of echo it to the form and then i'd do a query that if the user's input is the same as that of the last inserted,the record being echoed on the form will be inserted instead. and the table will look like

 

c_id | c_name

1    | BSA

1    | BSA

 

..(the c_id here is no longer unique)else if it's not the same,then

 

c_id | c_name

1    | BSA

2    | BEED

 

..what do you think?i still have an issue on how to code that though..any ideas? :-\

 

[geekisthenewsexy]

hi there,well i think i've solved it.

apparently, that solution worked. for that particular problem though.. :-\

i think the database is okay 'cause when the records there are going to be fetched for the drop downs

all i have to do is select the distinct on table course where there are duplicates.

but i have a problem on the form's output again.

 

after emptying all the tables, i tried adding a new record

 

table course:

_______________

c_id | c_name

1    | BEED

 

table year:

_____________________

y_id | c_id | year

1    | 1    |First year

 

table block:

___________________

b_id | y_id | block

1    | 1    | ED1-A

 

then using this query

$result = mysql_query("SELECT c_name,year,block FROM (course LEFT JOIN year ON course.c_id=year.c2_id) LEFT JOIN block ON year.c2_id=block.y2_id") or die(mysql_error());

 

and the code you posted which filters what i want to appear

<?php

$rs = mysql_query($sql) or die(mysql_error());

$prevName = '';
$prevYear = '';

while ($row = mysql_fetch_array($rs))
{



echo (($row['c_name'] == $prevName) ? '' : "<tr><td colspan='2'>".$row['c_name']).'</td></tr>';
    echo '<tr><td>'.(($row['year'] == $prevYear AND $row['c_name'] == $prevName)) ? '' : $row['year']).'</td><td>'.$row['block'].'</td></tr>';



$prevName = $row['c_name'];



$prevYear = $row['year'];
}

?>

, the form outputs:

_________________________

COURSE | YEAR | BLOCK

BEED      | First year | ED1-A

 

okay,so that's one record. but when i tried adding another record with the same course, year: Second year and added two blocks (ED1-A,ED1-B), the form now outputs:

 

________________________________

COURSE | YEAR | BLOCK

BEED      | First year | ED1-A

              | Second year | ED1-A

                    | ED1-A

                    | ED1-B

 

 

when it should have been just

________________________________

COURSE | YEAR | BLOCK

BEED      | First year | ED1-A

              | Second year | ED1-A

                    | ED1-B

 

this is what i get everytime i add another record with the same or not the same course,or year or number of blocks. it's just confusing

i just  wanted block and year to be in their right places (so frustrating)

 

i was thinking about changing my select query..and i'm guessing maybe because i removed the unique id on the table course that's why i'm having problems on the output again or something..do you have any ideas or suggestions about the querry?i'm seriously losing my  mind here..

 

[kickstart]

Hi

 

Sorry for the delay, been busy for a few days.

 

Your first attempt had a few errors in it. Firstly the "on duplicate key" needs to update c_id not just id.

 

The inner inserts you do once, returning $sql and then try and execute $sql as a query again.

 

You later post has code that is missing a bracket (or has an extra one) in the 2nd echo statement.

 

All the best

 

Keith

[fenway]

This is getter rather lengthy, and now it's regressing into the realm of syntactical php errors....

[geekisthenewsexy]

hi there, i'm back. I've been busy too.

So sorry fenway, i just posted a few php codes cause i thought maybe it would help you guys to further understand my problem..didn't mean to break the rules or anything..sorry.

but it's cool if you wanna move this or something if you find the real problem's with php..

but i think it's really with sql..

 

anyways,

You later post has code that is missing a bracket (or has an extra one) in the 2nd echo statement.

i see what you mean but it's from the code you posted.

mine's like this

 

$result = mysql_query("SELECT DISTINCT c_name,year,block FROM (course LEFT JOIN year ON course.c_id=year.c2_id)RIGHT JOIN block ON block.y2_id=year.c2_id") or die(mysql_error());  

 

and just refer to the attachment for the php code kickstart. don't want to break the rules again. 

 

 

[attachment deleted by admin]