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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
padek78
Forum Newbie
Posts: 1
Joined: Sun Oct 23, 2005 10:28 am

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

Post 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 :)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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
User avatar
dallasx
Forum Contributor
Posts: 106
Joined: Thu Oct 20, 2005 4:55 pm
Location: California

date time

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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:
Post Reply