Split MySQL total rows between 2 HTML tables

gigpacknaxe · #1 (**permalink**) 12-28-09, 11:44 AM

Hi,

Needless to say I am a newb. I'm running:

Windows XP Pro 32-bit
Apache 2.2
PHP 5.3
MySQL 5.1
phpMyAdmin 3.2

I have built a simple contact table that only has three columns: p_id, name, phone. I have also designed simple PHP scripts that allow me to insert and update records. What I want to do is sort the table by the name, split the data in half, and insert both data sets into two different HTML tables. I need the two HTML table to display side by side and on the same page. Table 1 would contain records A-? and Table two would contain ?-Z. I have built a simple PHP script that inserts the data into an HTML table row by row. Having MySQL know where the half way point is and starting a new table is where I am having my problem.

I am not asking that someone write me a script, but rather point me in the right direction, recommend other similar problems with solutions, or make some kind of suggestions. I didn't know what you would call what I am trying to do so I didn't know how to search for it. Thank you in advance for any help.

Kind Regards,

Joshua

PHP Code:


			
<?php



$con = mysql_connect("localhost","uname","pword");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("dbase_name", $con);



    $sqlExtra = "ORDER BY name, phone ASC";

    $result = mysql_query("SELECT * FROM contact_table $sqlExtra");

    $i = 1;

    function checkNum($i){

    return ($i%2) ? TRUE : FALSE;

    }        

    while($row = mysql_fetch_array($result))

    {



    if(checkNum($i) === TRUE){

        $bgcolor = "#FFFFFF";

    }else{

        $bgcolor = "#CCCCCC";    

    }

     

    echo "<tr id=\"plist-tr\" bgcolor=\"" . $bgcolor . "\">";        

    echo "<td height=\"30\"><a href=\"lhupdate.php?id=" . $row['p_id'] . "\">Edit</a>";

    echo "<td width=\"385\" height=\"30\">"; 

    if($name == $row['name']){

        echo "</td>";

    }else{

        echo $row['name'] . "</td>";

    }

    echo "<td width=\"385\" height=\"30\">" . $row['phone'] . "</td>";

    echo "</tr>";

    $i++;

    $name = $row['name'];

    }

}    

?>

This script only displays the part of the HTML table that is looped. It also alternates the background color of each row and it omits duplicate names.

wirehopper · #2 (**permalink**) 12-28-09, 12:49 PM

I'd use MySQL to count the number of records returned from the query with SELECT COUNT(*) FROM `table`;, or PHP's count function (count($aArray)) - then assemble the tables.

End User · #3 (**permalink**) 12-28-09, 12:53 PM

Quote:

Originally Posted by gigpacknaxe

What I want to do is sort the table by the name, split the data in half, and insert both data sets into two different HTML tables. I need the two HTML table to display side by side and on the same page. Table 1 would contain records A-? and Table two would contain ?-Z.

Do a COUNT() of all of the rows to be returned, divide by 2, and you'll have the "half-way point". Start outputing the data to the first HTML table, counting the rows as you go, and when you hit the mid-point, switch over to the second table.

j-a-m-i-n · #4 (**permalink**) 12-28-09, 12:55 PM

Hi Joshua,

I think you could work out how many rows you have in your table by using this function: mysql insert id, which will give you the auto incremented id of the last row added.

You could then divide that number by two (rounded up to the nearest whole number using ceil(); ).

Do this within your query of the database before echoing any data.

Then you could perhaps do a for loop to echo the format of the first table, whilst it's not reached your half way id (rounded up to the nearest whole number using ceil). Then simply do the same, starting with the half way id (+1) as the initial value and continue echoing everything from then until your last row which is obtained through your mysql_insert_id(); function (with the formatting and positioning of your second table).

I haven't used php for a while and am by no means anything more than a beginner but this seems like an option from my point of view.

Ben.

j-a-m-i-n · #5 (**permalink**) 12-28-09, 12:59 PM

Oops. Listen to those guys. They know what they're talking about. It's better using COUNT() as that will give you the total number of rows, relevant to your query, as you may not be processing every row your table.

gigpacknaxe · #6 (**permalink**) 12-29-09, 02:04 PM

First, Thank you to everyone who replyed to my question. All suggestions were good ones!!!

Ya'll recommended two different options:
COUNT()
mysql_insert_id()

I chose the explore the count method simply because as records are added and deleted the last ID might not be the actual total rows. After beating my head against the wall for quite some time, the answer did come to me. I will attach the script below and please feel free to make any suggestions you like......I am no Pro. I start by counting all rows then dividing by 2 to get the half way point. Next I use a while loop to loop through the mysql_fetch_array until the mid point is reached. I reset my counter variable to the midpoint then use a do....while loop to reach the other half of the rows. Now that it is done I feel that it was too simple of a task to have asked for help. Makes me feel dumb......lol

Pay no attention the echo's as that is where I will place the HTML code that I need looped. Current values are just for testing. Thank you again and here is the code:

PHP Code:


			
<?php



$con = mysql_connect("localhost","uname","pword");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("database_name", $con);



$result = mysql_query("SELECT *, COUNT(name) FROM lh_phone");

$result2 = mysql_query("SELECT * FROM lh_phone ORDER BY name, phone ASC");

$row = mysql_fetch_array($result);

$i = 1;

        

while (($row2 = mysql_fetch_array($result2)) && ($i <= round($row['COUNT(name)'] / 2)))

    

    {    

    echo "<br />";

    echo $i++ . "&nbsp;";

    echo $row2['name'];

    }     



$i = round($row['COUNT(name)'] / 2);

echo "<br />";



do

    {            

    echo "<br />";

    echo $i++. "&nbsp;";

    echo $row2['name'];

}while ($row2 = mysql_fetch_array($result2));        



mysql_close($con);        



?>

Joshua

job0107 · #7 (**permalink**) 12-29-09, 07:02 PM

What you are trying to do is relatively simple.
I am using mysql_num_rows().

Something like this:

PHP Code:


			
<html>
<head>
<style>
div{float:left;}
td
{
 width:100px;
 text-align:center;
 }
</style>
</head>
<body>
<?php
mysql_connect("localhost","username","password") or die('Could not connect: ' . mysql_error());
mysql_select_db("db-name");
$result = mysql_query("SELECT * FROM lh_phone ORDER BY name, phone ASC");
$rows = mysql_num_rows($result);
$sw = 0;
echo "<div><table border=1><tr><th>Name</th><th>Phone</th><th>Edit Record</th></tr>";
for($i=0;$i<$rows;$i++)
{
 $row = mysql_fetch_assoc($result);
 if($sw == 0 && $i > ceil($rows/2)-1){echo "</table></div><div><table border=1><tr><th>Name</th><th>Phone</th><th>Edit Record</th></tr>";$sw = 1;}
 echo "<tr><td>".$row["name"]."</td><td>".$row["phone"]."</td><td><a href='lhupdate.php?id=".$row['p_id']."'>Edit</a></td></tr>";
 }
echo "</table></div>";
mysql_close();
?>
</body>
</html>

gigpacknaxe · #8 (**permalink**) 12-30-09, 12:30 PM

Quote:

Originally Posted by job0107

What you are trying to do is relatively simple.
I am using mysql_num_rows().

Something like this:

PHP Code:


			
<html>

<head>

<style>

div{float:left;}

td

{

 width:100px;

 text-align:center;

 }

</style>

</head>

<body>

<?php

mysql_connect("localhost","username","password") or die('Could not connect: ' . mysql_error());

mysql_select_db("db-name");

$result = mysql_query("SELECT * FROM lh_phone ORDER BY name, phone ASC");

$rows = mysql_num_rows($result);

$sw = 0;

echo "<div><table border=1><tr><th>Name</th><th>Phone</th><th>Edit Record</th></tr>";

for($i=0;$i<$rows;$i++)

{

 $row = mysql_fetch_assoc($result);

 if($sw == 0 && $i > ceil($rows/2)-1){echo "</table></div><div><table border=1><tr><th>Name</th><th>Phone</th><th>Edit Record</th></tr>";$sw = 1;}

 echo "<tr><td>".$row["name"]."</td><td>".$row["phone"]."</td><td><a href='lhupdate.php?id=".$row['p_id']."'>Edit</a></td></tr>";

 }

echo "</table></div>";

mysql_close();

?>

</body>

</html>

Jerry,

Your code is definately simpler than what I had come up with, but I am confused as to how some of your code is working. I know it works because I have tested it. I understand how your query works, I understand mysql_num_rows (which is a really cool function, BTW), and I understand your loop. Can you give me a little more explaination of the if statement. Correct me if I am wrong but the for loop starts at zero and loops to the max record. But how does the if statement know how to restart. I think that is where I am getting lost or at least that general area.

Again, your code is much more eloquent than what I came up with. That's why I must learn how it works. The only thing I would have done differently is echo out each line of HTML but that is just a personal visual preference. Thank you for a great reply.

Joshua

Jcbones · #9 (**permalink**) 12-30-09, 02:43 PM

PHP Code:


			
//This syntax is what your confused about.

$i > ceil($rows/2) - 1

//$i increments on each loop.
ceil($rows/2) 
//returns $rows divided by 2 rounded up.
- 1 
//subtracts from the rounded value.

//if half of your rows was 20, then this statement would be
 $i > 19.

//So if $sw was equal to 0, and $i was equal to half of your rows, the if statement would run.

//After that if statement run, we change
 $sw to 1
// so the if statement would never validate again.

I hope that wasn't to complicated of an explanation.

gigpacknaxe · #10 (**permalink**) 12-30-09, 06:20 PM

Quote:

Originally Posted by Jcbones

PHP Code:


			
//This syntax is what your confused about.



$i > ceil($rows/2) - 1



//$i increments on each loop.

ceil($rows/2) 

//returns $rows divided by 2 rounded up.

- 1 

//subtracts from the rounded value.



//if half of your rows was 20, then this statement would be

 $i > 19.



//So if $sw was equal to 0, and $i was equal to half of your rows, the if statement would run.



//After that if statement run, we change

 $sw to 1

// so the if statement would never validate again.

I hope that wasn't to complicated of an explanation.

Not complicated at all. Very informative actually. When I saw $sw was being reset to 1, that was blowing my mind because I thought the if statement was executing its code every loop. I also though that the if statement housed both of the echos: the one inline and the one below. That is why the $sw == 0 confused me. After your explaination I looked deeper to notice the "}" at the end of the inline echo. That is when it hit me that the inline echo only ran once at the mid point of the loop and started the second div/table. Once I realized that it was like reading the matrix...I didn't see code any more. I just saw blonde, brunett, red head......lol. Thanks a million for all of you guys chipping in and helping me with this issue.

Joshua

PS - Is there anyway to mark this thread as solved, complete, or should I worry about it?