PHP/MySQL records per row

Way to have the records pulled from a mysql database display like this in a table:
record1 | record2
record3 | record4
I am going to assume you know what you are talking about when you say “records”, and that you don’t really mean “fields”. If you really mean “fields” you can adapt the code below to suit your needs.
If you use mysql_fetch_array(), you will find out that $row is an array that is twice the length of the number of fields you selected. Why is that? Because mysql_fetch_array() returns both a numerical array and an associative array so that you can use either numbers or field names as the array key. If you use mysql_fetch_row(), the array $row will be a numerical array the same length as the number of fields you selected, which will be more convenient in this case because you need to numerically loop through the fields of the record you selected, then stop when all the fields have been echoed.
If you want to print out two records per row in the table, you can use the modulus operator(%) to start new rows. The modulus operator returns the remainder after integer division. Just keep count of the number of records you have echoed, and if the number is evenly divisible by 2($record_count % 2 == 0), then start a new row.

<table>
<tr>
<?php
$record_count = 0; //Keeps count of the records echoed.
while ($row=mysql_fetch_row($sql_result))
{
//Check to see if it is time to start a new row
//Note: the first time through when
//$record_count==0, don't start a new row
if ($record_count % 2==0 AND $record_count != 0)
{
echo '</tr><tr>';
}

echo ‘<td>’;

//Echo out the entire record in one table cell:
for ($i=0; $i< count($row); $i++)
{
echo $row[$i];
}

echo ‘</td>’;

//Indicate another record has been echoed:
$record_count++;
}
?>
</tr>

</table>

Pagination in php

As your database grows, showing all the results of a query on a single page is no longer practical. This is where pagination comes in handy. You can display your results over a number of pages, each linked to the next, to allow your users to browse your content in bite sized pieces.

<?php

/*

Place code to connect to your DB here.

*/

include(’config.php’); // include your code to connect to DB.

$tbl_name=”"; //your table name

// How many adjacent pages should be shown on each side?

$adjacents = 3;

/*

First get total number of rows in data table.

If you have a WHERE clause in your query, make sure you mirror it here.

*/

$query = “SELECT COUNT(*) as num FROM $tbl_name”;

$total_pages = mysql_fetch_array(mysql_query($query));

$total_pages = $total_pages[num];

/* Setup vars for query. */

$targetpage = “filename.php”; //your file name (the name of this file)

$limit = 2; //how many items to show per page

$page = $_GET['page'];

if($page)

$start = ($page – 1) * $limit; //first item to display on this page

else

$start = 0; //if no page var is given, set start to 0

/* Get data. */

$sql = “SELECT column_name FROM $tbl_name LIMIT $start, $limit”;

$result = mysql_query($sql);

/* Setup page vars for display. */

if ($page == 0) $page = 1; //if no page var is given, default to 1.

$prev = $page – 1; //previous page is page – 1

$next = $page + 1; //next page is page + 1

$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.

$lpm1 = $lastpage – 1; //last page minus 1

/*

Now we apply our rules and draw the pagination object.

We’re actually saving the code to a variable in case we want to draw it more than once.

*/

$pagination = “”;

if($lastpage > 1)

{

$pagination .= “<div class=\”pagination\”>”;

//previous button

if ($page > 1)

$pagination.= “<a href=\”$targetpage?page=$prev\”>« previous</a>”;

else

$pagination.= “<span class=\”disabled\”>« previous</span>”;

//pages

if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up

{

for ($counter = 1; $counter <= $lastpage; $counter++)

{

if ($counter == $page)

$pagination.= “<span class=\”current\”>$counter</span>”;

else

$pagination.= “<a href=\”$targetpage?page=$counter\”>$counter</a>”;

}

}

elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some

{

//close to beginning; only hide later pages

if($page < 1 + ($adjacents * 2))

{

for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)

{

if ($counter == $page)

$pagination.= “<span class=\”current\”>$counter</span>”;

else

$pagination.= “<a href=\”$targetpage?page=$counter\”>$counter</a>”;

}

$pagination.= “…”;

$pagination.= “<a href=\”$targetpage?page=$lpm1\”>$lpm1</a>”;

$pagination.= “<a href=\”$targetpage?page=$lastpage\”>$lastpage</a>”;

}

//in middle; hide some front and some back

elseif($lastpage – ($adjacents * 2) > $page && $page > ($adjacents * 2))

{

$pagination.= “<a href=\”$targetpage?page=1\”>1</a>”;

$pagination.= “<a href=\”$targetpage?page=2\”>2</a>”;

$pagination.= “…”;

for ($counter = $page – $adjacents; $counter <= $page + $adjacents; $counter++)

{

if ($counter == $page)

$pagination.= “<span class=\”current\”>$counter</span>”;

else

$pagination.= “<a href=\”$targetpage?page=$counter\”>$counter</a>”;

}

$pagination.= “…”;

$pagination.= “<a href=\”$targetpage?page=$lpm1\”>$lpm1</a>”;

$pagination.= “<a href=\”$targetpage?page=$lastpage\”>$lastpage</a>”;

}

//close to end; only hide early pages

else

{

$pagination.= “<a href=\”$targetpage?page=1\”>1</a>”;

$pagination.= “<a href=\”$targetpage?page=2\”>2</a>”;

$pagination.= “…”;

for ($counter = $lastpage – (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)

{

if ($counter == $page)

$pagination.= “<span class=\”current\”>$counter</span>”;

else

$pagination.= “<a href=\”$targetpage?page=$counter\”>$counter</a>”;

}

}

}

//next button

if ($page < $counter – 1)

$pagination.= “<a href=\”$targetpage?page=$next\”>next »</a>”;

else

$pagination.= “<span class=\”disabled\”>next »</span>”;

$pagination.= “</div>\n”;

}

?>

<?php

while($row = mysql_fetch_array($result))

{

// Your while loop here

}

?>

<?=$pagination?>

How to reduce CPU overhead problem caused by MySql?

These type of problem occurs when your Site is accessed by more number of user with in second and that will bring your server down. Problem of CPU Overhead with MySql
Let me explain the problem clearly first,
We’re working on a website which was similar to http://www.swoopo.co.uk/ and as you can see clearly in that website the most recent data should be fetched form MySql database in each second. in the PHP page, which was being called from Ajax every second, had around 5 SQL queries to retrieve data from server.
First of all, I optimized that page reducing the 5 queries into single query by using left outer joins among 3 tables. And then, I did benchmark test by using Apache benchmark tool(ab) 50 request with concurrency of 50 with the following command.
ab -n 50 -c 50 http://xyz.com/ajax_page.php
And then in another SSH shell prompt, I run the top command to view the CPU usages parallelly.
I was still horrified that the CPU usages by mysql after 50 concurrent user was going out of control(100%) despite of optimized query. But many joins have been used in that single query and lots of data were there in those tables so the database overhead was high even though it was a single query.
How did I reduced the CPU overload drastically caused by MySql?
Now, the first challenge was to reduce the database access. It was clear that it was caused by concurrent database overhead in the PHP page which was being called in every second. Here is the simple steps what I did to reduce the database overhead.
1. I created another PHP file in which I’ve transferred the mysql query causing CPU overhead and called it from Cronjob.

2. Created a temporary table for storing the output given by PHP page called from CronJob.

3. Then, I scheduled the CronJob in every second, don’t tell me that CronJob can’t be run less than a minute, take a look at this post before saying this.And from each call, the output data was stored in the temporary table.

4. And, finally from the Ajax the a new PHP page was called which was only accessing the data from temporary table with single row.
I did the same benchmarking again to the newly made page(which is accessing data only from temporary table) and saw that CPU usages after the this process reduced drastically.
I know I can further reduce the CPU overhead by storing the temporary data in a text file.