Working with multi-dimensional arrays

[Love2c0de]

Good morning all,

 

I have been working on a website which allows users to register and upload files.

 

I have 3 tables currently -> 'users', 'demos' and 'games'.

 

I am trying to retrieve user information, and uploaded files information from the 2 tables. I use a select query to grab the information from both tables.

 

The aim is to display the latest 5 registered users and the latest 5 uploaded files on the homepage.

 

Here is my code so far:

<?php
//general database information - latest members, latest demo's uploaded,

$con = mysqli_connect("localhost", "root", "", "gaming") or die("Error connecting to the server.");
$qry = mysqli_query($con,"SELECT users.id,users.username,users.join_date,
                                               demos.gid,demos.game_id,demos.file_name,
                                               demos.demo_name,demos.demo_desc,
                                               demos.uploaded_by,demos.upload_date
                                   FROM users, demos
                                   WHERE users.id = demos.gid") or die("Error selecting data.");

$headings = array("Latest 5 Members","Lastest 5 Demos Uploaded");

$member = array();
$demo = array();

$c = 0;
$n = 0;
$i = 0;

while ($row = mysqli_fetch_row($qry))
{
   while($c < 3)
   {
       $member[$n][$c] = $row[$c];

       if ($c == 2)
       {
           $member[$n][$c] = date("d/m/Y", strtotime($member[$n][$c]));
       }

       $c++;
   }

   while($c < 10)
   {
       $demo[$n][$i] = $row[$c];

       if ($i == 6)
       {
           $demo[$n][$i] = date("d/m/Y", strtotime($demo[$n][$i]));
       }

       $c++;
       $i++;
   }

   $c = 0;
   $i = 0;
   $n++;
}

echo "<pre>";
print_r($member);
echo "</pre>";

echo "<pre>";
print_r($demo);
echo "</pre>";



?>

 

This is working fine and is creating 2 multi-dimensional arrays, splitting the specific information into the correct array.

 

The first inner while loop put's user information to the $member array.

The second inner while loop puts demorec information into the $demos array by looping through the rest of the $row, carrying on the incrementing from the first inner while.

 

I am seeking advice on a number of things:

1. Is my code for separating the data suitable or is it overkill? Is there an easier way to do this? As you can see 3 while loops seems a bit OTT to me but I'm not sure, it was the only way I could get it to work. I re-wrote what I'd previously done numerous times. About the query, I need to still put a LIMIT but I just wanted to get the data sorted first.
    
   
2. Are there particular functions I wuld find useful for working with multi-dimensional arrays? I've looked at php.net and other places but it seems I would use something similar to my code above, but rather than storing the data-set it up for being printed
   

Thank you for any replies and help.

 

Regard,

 

L2c.

Edited February 23, 2013 by Love2c0de

[Love2c0de]

Sorted it.

 

Her is the updated code:

<?php
//general database information - latest members, latest demo's uploaded,

$con = mysqli_connect("localhost", "root", "", "gaming") or die("Error connecting to the server.");
$qry = mysqli_query($con,"SELECT users.id,users.username,users.join_date,
                                               demos.gid,demos.game_id,demos.file_name,
                                               demos.demo_name,demos.demo_desc,
                                               demos.uploaded_by,demos.upload_date
                                   FROM users, demos
                                   WHERE users.id = demos.gid") or die("Error selecting data.");

$headings = array("Latest 5 Members","Lastest 5 Demos Uploaded");

$member = array();
$demo = array();
$mem_out = "";
$dem_out = "";
$c = 0;
$n = 0;
$i = 0;

while ($row = mysqli_fetch_row($qry))
{
   while($c < 3)
   {
       $member[$n][$c] = $row[$c];

       if ($c == 2)
       {
           $member[$n][$c] = date("d/m/y", strtotime($member[$n][$c]));
       }

       $c++;
   }

   while($c < 10)
   {
       $demo[$n][$i] = $row[$c];

       if ($i == 6)
       {
           $demo[$n][$i] = date("d/m/y", strtotime($demo[$n][$i]));
       }

       $c++;
       $i++;
   }

   $c = 0;
   $i = 0;
   $n++;
}

/*
echo "<pre>";
print_r($member);
echo "</pre>";

echo "<pre>";
print_r($demo);
echo "</pre>";
*/

$lengths = count($member);
$mem_len = count($member[0]);
$dem_len = count($demo[0]);

$mem_out = "<table>";
$mem_out .= "<tr>";
$mem_out .= "<th>No.</th>";
$mem_out .= "<th>Username</th>";
$mem_out .= "<th>Join Date</th>";
$mem_out .= "</tr>";

for ($i; $i < $lengths; $i++)
{
   $mem_out .= "<tr>";

       for ($c; $c < $mem_len; $c++)
       {
           $mem_out .= "<td>{$member[$i][$c]}</td>";

       }

   $mem_out .= "</tr>";
   $c = 0;
}

$i = 0;
$c = 1;

$mem_out .= "</table>";

$dem_out = "<table>";
$dem_out .= "<tr>";
$dem_out .= "<th>Game</th>";
$dem_out .= "<th>Demo Name</th>";
$dem_out .= "<th>Title</th>";
$dem_out .= "<th>Description</th>";
$dem_out .= "<th>Uploaded By</th>";
$dem_out .= "<th>Upload Date</th>";
$dem_out .= "</tr>";

for ($i; $i < $lengths; $i++)
{
   $dem_out .= "<tr>";

       for ($c; $c < $dem_len; $c++)
       {
           $dem_out .= "<td>{$demo[$i][$c]}</td>";

       }

   $dem_out .= "</tr>";
   $c = 1;
}

$dem_out .= "</table>";

?>

 

To be honest I don't like it, feels like a bad approach but it's working so now I'll try to improve the code because I know it definitely can be somehow..

 

Kind regards,

[DavidAM]

Since you are joining the two tables, if any user has more than one demo, that user will appear in your list twice. You would have to account for this possibility in your PHP code to prevent displaying duplicate user data.

 

When you put a LIMIT on that query, if a user has more than one demo, that user will be in there twice, so you will not have 5 unique users.

 

When you put the ORDER BY on that query. You will only get demos for the 5 most recent users that actually have an upload. And a user who registered long ago, but uploaded a new file just now, will NOT be in the list.

 

I really think you have to do two separate queries in order to get the unique and accurate data you are looking for.

 

SELECT users.id, users.username, users.join_date
FROM users
ORDER BY join_date DESC
LIMIT 5

SELECT demos.gid,demos.game_id,demos.file_name,
       demos.demo_name,demos.demo_desc,
       demos.uploaded_by,demos.upload_date
FROM demos
ORDER BY upload_date DESC
LIMIT 5

You could add a join in the second query to get the username to display with the demo if you want. You could add an (outer) join in the first query (and a group by) to get the last upload date or count of uploads if you want.

 

 

You are right in thinking it is over-complicated. Using two queries (and doing the date formatting in the query), you can eliminate the nested loops, so something like:

$sql = 'SELECT id, username, DATE_FORMAT(join_date, "%d/%m/%Y") AS join_date_str
FROM users
ORDER BY join_date DESC
LIMIT 5';
$qry = mysqli_query($con,$sql);
$member = array();
while ($row = mysqli_fetch_row($qry)) {
 $member[] = $row;
}

$sql = 'SELECT gid, game_id, file_name, demo_name, demo_desc,
uploaded_by, DATE_FORMAT(upload_date, "%d/%m/%Y") AS upload_date_str
FROM demos
ORDER BY upload_date DESC
LIMIT 5'
$qry = mysqli_query($con,$sql);
$demo = array();
while ($row = mysqli_fetch_row($qry)) {
 $demo[] = $row;
}

[Love2c0de]

Hi David,

 

Thank you very much for your reply, I knew there would be major flaws in my work but I couldn't see them at all. Your post was eye opening to say the least.

 

I will get to work on this now. I'll let you know how I get on!

 

Is it really necessary for the 2 queries though? I was told I should try to limit 1 query to each page if possible. I thought the reason of a join was to prevent having to run 2 queries to grab data from separate tables. Was it my 'WHERE user.id = demos.gid' part which was causing most of the duplicate issues?

 

The reason I added the WHERE clause is because when I ran the query without it, it would first iterate through the demos table 'g_id' 5 times, then iterate through the users 'id', giving me duplicated data as it was printing the same id, username and join_date for every demo, then it would iterate to the second id in the users table and re-print the 5 demos again but with the same username etc.

 

Love the way you have formatted the date within the query string as well, I had issues with the dates being returned as strings, even though they were stored within an 'int' table field at one point and this method of doing it prevents me from having to work with the multi-dim array, sending it all to a standard level array. When printing the data, I just need to limit the amount of data printed in each row to the required number, for example I can limit the $member iteration to 3 seeing as though I need only the id, username and join date. So after every 3 iterations, I print a new table row.

 

Thanks again David,

 

Kind regards,

 

L2c.

Edited February 24, 2013 by Love2c0de

[DavidAM]

Is it really necessary for the 2 queries though? I was told I should try to limit 1 query to each page if possible. I thought the reason of a join was to prevent having to run 2 queries to grab data from separate tables.

 

In this case, you are trying to retrieve two different sets of data; 1) a list of users and 2) a list of uploads. Each set requires a separate query. Don't try to artificially limit the number of queries on a page. Just get all of the data for a given dataset (users) in a single query. --- Have a look at the bottom of this page. Right now, it says "Queries: 15 queries". On a dynamic site, most pages will take more than one.

 

Was it my 'WHERE user.id = demos.gid' part which was causing most of the duplicate issues?

 

No, there was nothing wrong with your query, if you wanted a list of "demos" and the users that own them. That's called "joining" the tables and will be needed in pretty much every query that has multiple tables. Most people today use the JOIN syntax instead of putting it in the WHERE clause, but it works either way.

 

And Yes. Because of the JOIN any user with multiple demos was listed multiple times (once for each demo). Since your PHP was pulling only part of the data and treating it as a data set, you were getting duplicate users. If you really want a single query, you can do that, and then somehow filter the duplicates out. But it means more work for the script and a PITA if you ever need to make changes to that part of the code later.

 

The reason I added the WHERE clause is because when I ran the query without it, it would first iterate through the demos table 'g_id' 5 times, then iterate through the users 'id', giving me duplicated data as it was printing the same id, username and join_date for every demo, then it would iterate to the second id in the users table and re-print the 5 demos again but with the same username etc.

 

This is called a Cartesian Product. Without a JOIN in the query, every row from every table will match every row in every table in the query (well, at least those that satisfy any other WHERE conditions). You get huge result-sets with these when it happens --- a table with 1,000 rows and a table with 500 rows will return 500,000 rows if there is no JOIN.

 

 

I look forward to hearing of your success. (or any further questions).

[Love2c0de]

Thank you for your detailed response.

 

I will keep re-reading this thread, I decided to go with your code as it made much more sense, minimized the code written and is generally more readable. I have carried on and created a news table and added content to be displayed. Trouble with this now is I have 3 queries on the page and I am beginning to notice this on page load.

 

My updated code is here:

<?php
//general database information - latest members, latest demo's uploaded,

$headings = array("Latest 5 Members","Lastest 5 Demos");
$member = array();
$demo = array();

$path = "core/demos/";
$dynamic_home = "";

$c = 0;
$i = 0;

$con = mysqli_connect("localhost", "root", "", "gaming") or die("Error connecting to the server.");

$sql = 'SELECT username, DATE_FORMAT(join_date, "%d/%m/%y") AS join_date_str
        FROM users
        ORDER BY join_date
        DESC
        LIMIT 5';
$qry = mysqli_query($con, $sql);
while ($row = mysqli_fetch_row($qry))
{
 $member[] = $row;
}

$sql = 'SELECT game_id, file_name, demo_name, demo_desc, uploaded_by,
        DATE_FORMAT(upload_date, "%d/%m/%y") AS upload_date_str
        FROM demos
        ORDER BY upload_date DESC
        LIMIT 5';
$qry = mysqli_query($con, $sql);
while ($row = mysqli_fetch_row($qry))
{
 $demo[] = $row;
}

$lengths = count($member);
$mem_len = count($member[0]);
$dem_len = count($demo[0]);

$dynamic_home = "<h4 class='stat_head'>{$headings[0]}</h4>";
$dynamic_home .= "<table id='table1'>";
$dynamic_home .= "<tr>";
$dynamic_home .= "<th>Username</th>";
$dynamic_home .= "<th>Join Date</th>";
$dynamic_home .= "</tr>";

for ($i; $i < $lengths; $i++)
{
   $dynamic_home .= "<tr>";

       for ($c; $c < $mem_len; $c++)
       {
           $dynamic_home .= "<td>{$member[$i][$c]}</td>";

       }

   $dynamic_home .= "</tr>";
   $c = 0;
}

$c = 0;
$i = 0;

$dynamic_home .= "</table>";

$dynamic_home .= "<h4 class='stat_head'>{$headings[1]}</h4>";
$dynamic_home .= "<table id='table2'>";
$dynamic_home .= "<tr>";
$dynamic_home .= "<th>Game</th>";
$dynamic_home .= "<th>Demo Name</th>";
$dynamic_home .= "<th>Title</th>";
$dynamic_home .= "<th>Description</th>";
$dynamic_home .= "<th>Uploaded By</th>";
$dynamic_home .= "<th>Upload Date</th>";
$dynamic_home .= "</tr>";

for ($i; $i < $lengths; $i++)
{
   $dynamic_home .= "<tr>";

       for ($c; $c < $dem_len; $c++)
       {
           if ($c == 1)
           {
               $dynamic_home .= "<td><a href='{$path}{$demo[$i][$c]}'>{$demo[$i][$c]}</a></td>";
           }
           else
           {
               $dynamic_home .= "<td>{$demo[$i][$c]}</td>";
           }

       }

   $dynamic_home .= "</tr>";
   $c = 0;
}

$dynamic_home .= "</table>";

$dynamic_home .= "<h4 class='stat_head'>News & Updates</h4>";
$dynamic_home .= "<div id='news_div'>";

$sql = 'SELECT id, news_title, news_desc,
         DATE_FORMAT(news_date, "%W %D %M %x") AS news_date_str
         FROM news
         ORDER BY news_date ASC
         LIMIT 5';
$qry = mysqli_query($con, $sql);
while ($row = mysqli_fetch_assoc($qry))
{
   $dynamic_home .= "<h4 class='news_title'>{$row['news_title']} - <span class='news_date'>{$row['news_date_str']}</span></h4>";
   $dynamic_home .= "<p class='news_desc'>{$row['news_desc']}</p>";
}

$dynamic_home .= "</div>";



mysqli_close($con);
?>

 

I am trying to count the number of rows in the users table so that I can display some statistics in a sidebar. I've tried adding the mysql COUNT() function into my 'users' select query, but it seems to overwrite my username field and it only prints one row of data. It is printing the correct number of rows but seems to be breaking my query.

 

How can I count the number of rows within that query? I would rather grab that number now while I am querying that table rather than having to do another query just to count the number of rows.

 

Thanks once again much appreciated.

 

Kind regards,

 

L2c.

[Love2c0de]

Edit: It's not overwriting my username field. I am getting the correct data but my only issue now is that my for loop only seems to be printing out once and then exiting.

 

Edit: Seems that the query is only returning 1 row of data. I execute a print_r on the $member array just after fetching the data in the while loop and it's printing out only the first row of data.

 

Sorry,

 

Regards,

 

L2c.

Edited February 24, 2013 by Love2c0de

[Christian F.]

mysqli_num_rows () is what you're looking for, not MySQL's COUNT().

 

Also, why are you using one loop to build an array (and nothing else), and then another to loop through said array? Why not just process the data directly, without using that intermediary array? Will save a bit of time, and memory, but most importantly: It'll strip away unnecessary code.

 

As for the loading times: If you're noticing increased load times from just this, then your servers are either seriously overworked in general, or seriously misconfigured. Either that, or you're doing something wrong. Like connecting to a DB server over the internet, or something similarly time-consuming.

[Love2c0de]

I wanted to count the full total of users which re registered but my query limits it to return only 5 rows, so I need a way to count the total number of rows in the database.

 

That's a very good point, I'll process the data within the while when I retrieve the information from the database.

 

I'm literally running the code on my localhost, although I do get a configure error message everytime I load up phpMyAdmin.

 

Kind regards,

 

L2c.

Edited February 24, 2013 by Love2c0de

[DavidAM]

I don't see the COUNT in your query; but you are correct, it will not work that way. COUNT is an aggregate function -- it operates over a GROUP of rows NOT on individual rows. If you want the total row count in the table, there are two things you can do. 1) Run another query: SELECT COUNT(*) FROM users or 2) add SQL_CALC_FOUND_ROWS to your existing query, then run another query SELECT FOUND_ROWS() to get the answer. SQL_CALC_FOUND_ROWS will calculate how many rows would have been returned without the LIMIT on the query; and FOUND_ROWS() will return that value. (mysqli_num_rows()) is only going to tell you how many rows were returned by the query).

 

# NOTE there is no comma after SQL_CALC_FOUND_ROWS
$sql = 'SELECT SQL_CALC_FOUND ROWS username, DATE_FORMAT(join_date, "%d/%m/%y") AS join_date_str
                FROM users
                ORDER BY join_date DESC
                LIMIT 5';
$qry = mysqli_query($con, $sql);
while ($row = mysqli_fetch_row($qry))
{
 $member[] = $row;
}
mysqli_free_result($qry);

$qry = mysqli_query($con, 'SELECT FOUND_ROWS()');
$row = mysqli_fetch_row($qry);
$totalUsers = $row[0];
mysqli_free_result($qry);

 

One thing I didn't mention, you should get in the habit of releasing the resultset once you are finished with it. I added calls to mysli_free_result in the example above.

 

I don't see anything here that should cause significant performance problems. If you have a configuration issue, you may want to get that resolved. My development machine is a very low-end computer (one I literally got off the trash pile). I don't worry too much about performance on this machine. Or, I should say, I realize that performance on this machine will be slower than a production server. I just try to keep everything as efficient as possible. When you look at the performance of a "page" you have to consider a lot of things: network time (between the click on a link and the time it gets to the web server); web-server time (web-server gets the request and decides to process it and sends it off to PHP); PHP time (that's your script, plus any PHP loading overhead); more web-server time (web-server gets the results from PHP and sends it to the browser); more network time; browser time (formatting the page).

 

If you are really concerned about your script's performance have it tell you how long it took. At (near) the very beginning of the script add this line of code:

$startTime = microtime(true);

Then at the end, add this:

$duration = microtime(true) - $startTime;
$dynamic_home .= sprintf('<P>Script Duration: %.2f secs</P>', $duration);

[Love2c0de]

Your posts have been invaluable to me. Thank you so much for taking the time to explain in great detail about my specific problem.

 

Unfortunately I think I have stumbled upon a pretty bad problem with my site setup. I have 1 main index.php page where I load templates containing HTML into the specific areas. I have a backend php script which searches the directory and depending on the value of a $_GET variable, will load the specific page required.

 

Here is that page:

<?php
session_start();
require("core/initialize.php");

if(!isset($_SESSION['username']) && $_GET['page'] == "home")
{
require("core/db_queries/main_query.php");
}

if(isset($_POST['reg_username']))
{
require('core/register.php');
}

if(isset($_POST['login_username']))
{
require('core/login.php');
}

if(isset($_POST['demo_name']))
{
require('core/upload.php');
}

if(isset($_GET['page']) && $_GET['page'] == 'logout')
{
require('core/logout.php');
}

if(isset($_SESSION['username']))
{
$member_dashboard = "<div id='dashboard'>";
$member_dashboard .= "<span>Welcome, {$_SESSION['username']}</span>";
$member_dashboard .= "<a href='?page=profile'><img src='images/my_profile.jpg' alt='Profile' title='Profile' /><br />Profile</a>";
$member_dashboard .= "<a href='?page=upload'><img src='images/upload_icon.png' alt='Upload a Demo' title='Upload' /><br />Upload</a>";
$member_dashboard .= "<a href='?page=write_review'><img src='images/review_icon.jpg' alt='Write Review' title='Write Review' /><br />Write Review</a>";
$member_dashboard .= "<a href='?page=my_demos'><img src='images/disc_icon.jpg' alt='My Demos' title='My Demos' /><br />My Demos</a>";
$member_dashboard .= "</div>";

$nav_link = "<li><a href='?page=upload'>Upload A Demo</a></li><li><a href='?page=my_demos'>My Demos</a></li><li id='logout'><a href='?page=logout'>Logout</a></li>";
}
else
{
$form = "<form name='quick_login' method='post' action='?page=home'>";
$form .= "<input type='text' placeholder='username...' name='login_username' />";
$form .= "<input type='password' placeholder='password...' name='login_password' />";
$form .= "<input type='submit' name='submit' value='Login' />";
$form .= "</form>";

$form .= "<a href='?page=register' class='form_links green'>Need to Register?</a>";
$form .= "<a href='?page=reset_pass' class='form_links red'>Forgot your login?</a>";
}


include("core/sidebars.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Demo Central - <?php print($title); ?></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="description" content="Some description here." />
<script type="text/javascript" src="js/jquery-1.8.3.js"></script>
<link rel="stylesheet" type="text/css" href="styles/gaming_styles.css" />
<script type="text/javascript">
$(function(){
$('#navigation li').hover(function(){
$(this).css("background-image","url('images/menu_hover.jpg')");
},function(){
$(this).css("background-image","url('images/background_img.jpg')");
});
});
</script>
</head>

<body>

<div id="header">

<a href="?page=home">
<img src="images/gaming_logo.png" alt="Logo" title="Gaming Logo" id="logo" />
</a>

<?php
if(isset($quick_error)){print($quick_error);}
if(isset($form)){print($form);}//contains the 2 links also
if(isset($member_dashboard)){print($member_dashboard);}
?>
</div>

<ul id="navigation">
<!--list items must be on one line because browsers create space between links if they are each on a separate line.-->
<li><a href="?page=home">Home</a></li><li><a href="?page=games">Games</a></li><li><a href="?page=reviews">Reviews</a></li><li><a href="?page=feedback">Feedback</a></li><li><a href="?page=contact">Contact</a></li><?php if(isset($nav_link)){print($nav_link);}?>
</ul>

<div id="content_wrapper">

<div id="left_container">
<?php if(isset($left_sidebar)){print($left_sidebar);} ?>
</div>

<div id="right_container">
<?php if(isset($right_sidebar)){print($right_sidebar);} ?>

</div>

<div id="container">
<?php include($output); ?>
<?php if(isset($dynamic_home) && $_GET['page'] == "home"){print($dynamic_home);} ?>
</div>
</div>

</body>
</html>

 

Now, as you can see, I execute this at the top of my page:

if(!isset($_SESSION['username']) && $_GET['page'] == "home")
{
require("core/db_queries/main_query.php");
}

 

The main_query.php is the file which I have been posting about all along. At the bottom of this script I have added this:

$num_mem = $member[0][2];
$num_dem = $demo[0][6];

 

These values as you can guess return the total values of users and demorecs. BUT, I need to display this in the left navigation bar. I have a backend php script which sets up the data for the sidebars (left and right). This is included in my index.php page just before the HTML DOCTYPE (I put it here to make sure that $num_mem and $num_dem variables are set). They are set and they are printing out ok.

 

Trouble is I want that information in the sidebars (site stats) to display ONLY if the user is not logged in, as soon as the session is set, I change the data to user-related information (or as is the plan). I have taken the "&& $_GET['page'] == 'home'" out of the if statement to execute the main_query.php as I want it to display this info on all pages except when the user logs in. I can get around this problem by leaving my print out code like this:

<?php if(isset($dynamic_home) && $_GET['page'] == "home"){print($dynamic_home);}  ?>

 

But this is dirty as it will still do the queries but won't print the end result. This is obviously not my desired result. I know I can target if the user is logged in and what page they are on but it doesn't feel right. There must be a simpler way. Do I just add a new query to my sidebars.php script?

 

It just all seems jumbled up at the moment, queries here and there. Would it be best to have one main query file containing many different queries within say a switch statement, which I just call when I need, passing in a value to the switch to select the correct query (and of course have the 'most used' queries in a certain block of code)?

 

Getting in a muddle, I stayed up from 2am this morning and completed all this round early morning so I've not had much chance to take it in properly. I knew there was a problem this morning and it's been on my mind.

 

Do you have any thoughts for me or opinions?

 

Kind regards,

 

L2c.

Edited February 24, 2013 by Love2c0de