Page 1 of 1

Please help my with creating an array to be able to sort it

Posted: Sun Oct 23, 2005 11:11 am
by padek78
Hello everyone! I'm new to this community and I'm also quite "fresh" in php coding . I have a problem and i need to sort it out quite fast...

I have a database which is build of lets say 4 tables. Each table has the same structure of fields. One of this fields is a data/time field.
Now. Lets say there are 3 records in each table.
And the question is how to gather all this records from all tables into one php array and sort this array by data/time field ?

For now I can only do that (and I now that it is not very effective but works fine):
1. Create a function which connect to database and transfere a query to database

Code: Select all

<?php
function connect_and_do($qu)
{
	$host = 'localhost';
	$user = 'root';
	$pass = '';
        $db_name = 'my_database';
	
	$conn = mysql_connect($host, $user, $pass);
	mysql_select_db($db_name);
	$result = mysql_query($qu);
	mysql_close();
	return $result;
}
?>
2. Creating my queries

Code: Select all

$query1_SQL = "SELECT * FROM table1 WHERE field1 = '".$some_value."';";
$query2_SQL = "SELECT * FROM table2 WHERE field1 = '".$some_value."';";
$query3_SQL = "SELECT * FROM table3 WHERE field1 = '".$some_value."';";
$query4_SQL = "SELECT * FROM table4 WHERE field1 = '".$some_value."';";
3. Call my function 4 times because there is 4 tables in my db

Code: Select all

$result1 = connect_and_do ( $query1_SQL );
$result2 = connect_and_do ( $query2_SQL );
$result3 = connect_and_do ( $query3_SQL );
$result4 = connect_and_do ( $query4_SQL );
4. And printing it all

Code: Select all

print ( "<table border = \"1\">" );

while ( $draw1 = mysql_fetch_row ( $result1 ))
{
    print ( "<tr>" );
    for ( $i = 0; $i < count ( $draw1 ); $i++ )
    {
        print ( "<td width = \"150\">".$draw1[$i]."</td>" );
    }
    print ( "</tr>");
}

while ( $draw2 = mysql_fetch_row ( $result2 ))
{
    print ( "<tr>" );
    for ( $j = 0; $j < count ( $draw2 ); $j++ )
    {
        print ( "<td width = \"150\">".$draw2[$j]."</td>" );
    }
    print ( "</tr>");
}

while ( $draw3 = mysql_fetch_row ( $result3 ))
{
    print ( "<tr>" );
    for ( $k = 0; $k < count ( $draw3 ); $k++ )
    {
        print ( "<td width = \"150\">".$draw3[$k]."</td>" );
    }
    print ( "</tr>");
}



while ( $draw4 = mysql_fetch_row ( $result4 ))
{
    print ( "<tr>" );
    for ( $l = 0; $l < count ( $draw4 ); $l++ )
    {
        print ( "<td width = \"150\">".$draw4[$l]."</td>" );
    }
    print ( "</tr>");
}
print ( "</table>" );

Its pretty complicated and this complication is probably unnessesery I know :) but for the moment Im stil learning and can't using clases or objects just to make thing more simple.

Anyway the result is almost what I would like except one detail :) . I said erlier that there is 3 records in each table. So the result will be 3 x 4 tables = 12 records in html table. One field of this table is data/time . Now how to sort all those mysql records to print them in html table from the most early to the most late ?? Probably there sholud be some array in php to store all those records but there was no problem if in each table would be only one record, but there are 3 of them and i dont know how to aproach to the problem :(

Sorry for so huge post :) Please help newby :)

Posted: Sun Oct 23, 2005 11:53 am
by shiznatix
a few problems.

first off you don't have to connect to mysql then close with each query. just open the connection at the begining of the script then run all your queries then as soon as your script is done it will close the mysql connection. im sure that can be slowing things down.

2nd.

Code: Select all

while ( $draw1 = mysql_fetch_row ( $result1 ))
{
    print ( "<tr>" );
    for ( $i = 0; $i < count ( $draw1 ); $i++ )
    {
        print ( "<td width = \"150\">".$draw1[$i]."</td>" );
    }
    print ( "</tr>");
}
can be much simplier by doing

Code: Select all

while ( $draw1 = mysql_fetch_row ( $result1 ))
{
    echo "<tr>";
    echo "<td width = \"150\">".$draw1['field_name']."</td>";
    echo "</tr>";
} 

//where it says field_name put in the field that you are echoing from your mysql table.
last with the sorting. you can use mysql to sort it for you. look at ORDER BY

date time

Posted: Sun Oct 23, 2005 12:51 pm
by dallasx
For a date time field, I use a VARCHAR(8) and store a date like 1/12/2005. Of course I have my scripts format it just right so it will slip it in there error proof.

Posted: Sun Oct 23, 2005 12:55 pm
by John Cartwright
Instead of doing 4 queries, do 1..

Code: Select all

SELECT * FROM `table1`
INNER JOIN `table2` USING (`field1`) 
INNER JOIN `table3` USING (`field1`) 
INNER JOIN `table4` USING (`field1`) 
WHERE `table1`.`field1` = '1'
ORDER BY `table1`.`date`
I'm sure some one more experienced with mysql will be able to aid this query furthur..
:wink: