Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display info in columns

Status
Not open for further replies.
bar2ayunie

bar2ayunie

Programmer
Apr 20, 2009
17
NL
Hello, I have a php code to display a few things from my sql database. Right now, it's listed in rows:
item 1
item 2
item 3
and so on

But I want to list all of the info in rows AND two columns, like this:
item 1 item 2
item 3 item 4
and so on

I've looked endlessly on google to find codes that will get me to create another column and I did find a few, but I can't seem to get it inserted in the code that I have. I did already manage to have only images be displayed in two columns and as many rows as there are images to display, but I can't seem to add in more information. I keep getting errors.

Is there anybody who knows how to continue? I wil display on the code that works perfectly, which is my starting code and that is the code that needs to be edited in order to display the items in two columns and rows.

This is de code I currently have:

Code: 
(connecting to sql data)

$connection = mysql_connect("$hostname" , "$user" , "$pass") or die ("Can't connect to MySQL");
$db = mysql_select_db($dbase , $connection) or die ("Can't select database.");

$q = "select * from graphics_textures order by id desc ";
$result= mysql_query($q, $connection) or die
("Could not execute query : $q." . mysql_error());

$rows_per_page=10; 
$total_records=mysql_num_rows($result);
$pages = ceil($total_records / $rows_per_page);
$screen = $_GET["screen"];
if (!isset($screen))
$screen=0;
$start = $screen * $rows_per_page;
$q .= "LIMIT $start, $rows_per_page";
$result= mysql_query($q, $connection) or die
("Could not execute query : $q." . mysql_error());

while ($row=mysql_fetch_array($result))
{
$id=$row["id"];
$date=$row["date"];
$title=$row["title"];
$artist=$row["artist"];
$thumb=$row["thumb"];
$preview=$row["preview"];
$download=$row["download"];
$pcounter=$row["pcounter"];
$dcounter=$row["dcounter"];
?>


<table cellspacing="0" width="480"><tr><td>

<br><br>

</td></tr><tr><td>

<img src="<?php echo "$thumb"; ?>" align="left">

<b>ID:</b> <?php echo "$id"; ?><br>
<b>Title:</b> <?php echo "$title"; ?><br>
<b>Artist:</b> <?php echo "$artist"; ?><br>
<b>Date:</b> <?php echo "$date"; ?><br>
<a href="../../Downloads/Textures.php?id=<?php echo "$id"; ?>">Download</a> 
- [<?php echo "$dcounter"; ?>] <br>
</td></tr></table>
</td></tr></table>


<?php
} #end of while
?>

<p align="center">

<?php
if ($screen > 0) 
$j = $screen - 1;
$url = "Textures.php?screen=$j"; // EDIT change 'layout.php' if your page is different
echo "<a href=\"$url\">Prev</a>"; // EDIT change word 'Prev' if desired differently


for ($i = 0; $i < $pages; $i++) {
$url = "Textures.php?screen=" . $i; // EDIT change 'layout.php' if your page is different
$j = $i + 1;
echo "  <a href=\"$url\">$j</a>  ";
}

if ($screen < $pages-1) {
$j = $screen + 1;
$url = "Textures.php?screen=$j"; // EDIT change 'layout.php' if your page is different
echo "<a href=\"$url\">Next</a>";  // EDIT change word 'Next' if desired differently
}

?>

Thank you very much!
 
Sort by date Sort by votes
what version of mysql are you using?
please post a sample of twenty records from your database table in sql insert format, together with a sql create statement.
 
Upvote 0 Downvote
I am using MySQL version 5.0.32.

CREATE TABLE `affiliates` (
`id` int(4) NOT NULL auto_increment,
`url` varchar(250) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`this_month` int(5) NOT NULL default '0',
`last_month` int(5) NOT NULL default '0',
`month_count` tinyint(2) NOT NULL default '0',
`total` int(6) NOT NULL default '0',
PRIMARY KEY (`id`)
)

There are two ways I use them, either with ' or without, here are both ways. I'm not sure if that matters (I thought it didn't), but I created the graphics_textures table using the second way.



CREATE TABLE `graphics_textures` (
`id` mediumint(5) NOT NULL auto_increment,
`date` date NOT NULL default '',
`title` varchar(50) NOT NULL default '',
`artist` varchar(50) NOT NULL default '0',
`thumb` varchar(255) NULL default '0',
`preview` varchar(255) NOT NULL default '0',
`download` varchar(255) NOT NULL default '0',

`pcounter` int(5) NOT NULL default '0',

`dcounter` int(5) NOT NULL default '0',
PRIMARY KEY (`id`)
)


or



CREATE TABLE graphics_textures (
id mediumint(5) NOT NULL auto_increment,
date date NOT NULL,
title varchar(50) NOT NULL,
artist varchar(50) NOT NULL,0
thumb varchar(255),
preview varchar(255) NOT NULL,
download varchar(255) NOT NULL,
pcounter int(5) NOT NULL,
dcounter int(5) NOT NULL,
PRIMARY KEY (ID)
);



And to insert my rows (which I normally do manually through the insert option in MySQL and just insert the options, not using a query), but here is the query for 20 rows:





INSERT INTO graphics_textures
VALUES('1', '20009-04-18','Blue Dots','Yunie', ' ' 'INSERT INTO graphics_textures
VALUES('2', '20009-04-18','Blue Silk','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('3', '20009-04-18','Color Bars','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('4', '20009-04-18','Colored Fire','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('5', '20009-04-18','Purple Tiles','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('6', '20009-04-18','Earth','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('7', '20009-04-18','Green Fun','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('8', '20009-04-18','Gold','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('9', '20009-04-18','Purple Liquid','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('10', '20009-04-18','Wood','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('11', '20009-04-18','Light Silk','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('12', '20009-04-18','Water Effect','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('13', '20009-04-18','Red Blur','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('14', '20009-04-18','Crazy Strokes','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('15', '20009-04-18','Rusty Grunge','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('16', '20009-04-18','Dirty Red','Yunie', ' ' 'INSERT INTO graphics_textures
VALUES('17', '20009-04-18','Plasma','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('18', '20009-04-18','Rendering Water','Yunie', ' ' 'INSERT INTO graphics_textures
VALUES('19', '20009-04-18','Energy','Yunie', ' ' '
INSERT INTO graphics_textures
VALUES('20', '20009-04-18','Drops','Yunie', ' ' '
I hope this is what you meant?
 
Upvote 0 Downvote
Ah sorry forget the first 'create' table, I took one from another table (the first one on my list), then I thought I'd better take the one that's linked to the page we're talking about. I forgot to delete it. Sorry and now I see that I could better dropped the codes into a code table, but I don't see how to edit the post. So sorry for that.
 
Upvote 0 Downvote
this code works fine for me. i did not change any of the query language so I cannot explain why the code shows, on your system, all the rows rather than just the limit thereof.

i suggest you go back to basics and just have the code standalone outside of your page structure until you are comfortable that it works for you. then try integrating it into the overall page.

Code: 
<?php
mysql_connect('', '', '') or die (mysql_error());
mysql_select_db('') or die (mysql_error());
$limit = 10;
$table = 'graphics_textures';
//get requested page
$page = empty($_GET['page']) ? 1 : (int) $_GET['page'];
//calculate offset
$offset = ($page - 1) * $limit;

//construct query
$query = "Select SQL_CALC_FOUND_ROWS * from $table LIMIT $limit OFFSET $offset";

//execute query
$result = mysql_query($query) or die (mysql_error());
$cResult = mysql_query("Select found_rows()") or die(mysql_error());
list($count) = mysql_fetch_array($cResult, MYSQL_NUM);

$pageNavBar = getPages($limit, $count, $page);
//start the output
echo <<<HTML
<style type="text/css">
/*style links */
.pageNav a {text-decoration:none; cursor:hand; color: #ad85ff;}
.pageNav a:visited {text-decoration:none; cursor:hand; color: #ad85ff;}
/*make the page numbering smaller generally */
.pageNav{ font-size:smaller;}
.page{ border: thin dotted #060409; background-color:#fdffeb;}
.nav{ padding: 5px; margin: 2px; width: 2em;}
.prev{}
.last{ background-color:blue;}
.first{ background-color:blue;}
.next{}
.active {backround-color:grey; color:red;}
.select select {}


</style>
<table width="100%" border="1" rules="groups">
<thead>
<tr>
    <th colspan="3">
        $pageNavBar
    </th>
</tr>
</thead>
<tbody>
HTML;
while ($row = mysql_fetch_assoc($result)){
    echo <<<HTML

<tr>
    <td align="center" width="33%">{$row['id']}</td>
    <td align="center" width="33%">{$row['title']}</td>
	<td align="center" width="33%">{$row['artist']}</td>
</tr>
HTML;
}
echo <<<HTML
</tbody>
<tfoot>
<tr>
    <th colspan="3">
        $pageNavBar
    </th>
</tr>
</tfoot>
</table>
HTML;

function getPages($limit, $count, $page){
    //put the url into a variable
    $s = "[URL unfurl="true"]http://"[/URL] . $_SERVER['HTTP_HOST'] .'/'.  ltrim($_SERVER["SCRIPT_NAME"] ,'/');
    //calculate the number of pages needed
    $nPages = ceil($count/$limit);
    //do the first/last prev/next buttons
    $first = <<<HTML
<span class="first nav">
	<a href="$s?page=1"><<<</a>
</span>
HTML;
    $last = <<<HTML
<span class="last nav">
	<a href="$s?page=$nPages">>>></a>
</span>
HTML;
    if ($page  > 1) {
    	$p = $page - 1;
		$prev = <<<HTML
	<span class="next nav">
		<a href="$s?page=$p"><-</a>
	</span>
HTML;
	} else {
		$prev = '&nbsp;';
		$first = '&nbsp;';
	}
	if ($page < $nPages) {
    	$p = $page + 1;
		$next = <<<HTML
	<span class="next nav">
		<a href="$s?page=$p">-></a>
	</span>
HTML;
	} else {
		$next = '&nbsp;';
		$last = '&nbsp;';
	}
    
    //now construct the pages
    //if more than 10 then use a select
    if ($nPages > 10){
        $output = <<<HTMLJS
<span class="nav select">
    <select name="page" onchange="window.location='{$s}?page=' + this.options[this.selectedIndex].value;">
HTMLJS;
    
        for ($p=1; $p <=$nPages; $p++){
            $output .= "<option value=\"$p\">$p</option>";
        }
        $output .= '</select></span>';
    } else {
        $output = '';
        for ($p=1; $p<=$nPages; $p++){
            $active = ($p == $page) ? 'active' : '';
            $output .= "<span class=\"nav page {$active}\"><a href=\"$s?page=$p\">$p</a></span>";
        }
    }
    return '<div class="pageNav">' . $first . $prev. $output . $next .  $last . '</div>';
}
?>
 
Upvote 0 Downvote
Okay, I know why the paging didn't work... since the code wasn't integrated into the rest of the code we talked about above (beginning of thread). If you check the page again: (newest version)

You can see the paging code at the top of the page and the previous code at the bottom. (so yeah, the info is there twice) I personalised the paging code a little so it shows the info the way that I want, however, it messes up with the coding for the second column, as the info is there twice per row. (also I couldn't seem to fix the download link, but that's for later worries) Plus, the code shows the info from the start so it shows from ID = 1 to the last ID, showing 10 rows (last is perfect). But I wanted it to start with the last ID.

Since nothing from the original code was changed (bottom part), that's the reason why it was still showing all items and not 10 rows.

Do you know what I'm saying? And do you have any idea how to fix it? I'm sorry if I ask too much, but I still seem to mess up when I want to perfect the code. Basically, what I want is the following: 2 columns, 10 rows per page, start with last ID and show the info like the way I've set them up as you view the page.

To let you know what I changed, I'll display the code that I've changed here.

I added a line to the CSS part:
Code: 
.table {font-family: verdana; font-size: 12px;}

And this is what I made of the rows:
Code: 
<table width="100%" border="1" rules="groups" <table class="table">
<thead>
<tr>
    <th colspan="4">
        $pageNavBar
    </th>
</tr>
</thead>
<tbody>
HTML;
while ($row = mysql_fetch_assoc($result)){
    echo <<<HTML

<tr>
    <td align="left" width="20%"><img src={$row['thumb']}></td>
	<td align="left" width="30%"><b>ID:</b> {$row['id']}<br>
    <b>Title:</b> {$row['title']}<br>
    <b>Artist:</b> {$row['artist']}<br>
	<b>Date:</b> {$row['date']}<br>
	<a href="../../Downloads/Textures.php?id=<?php ['id']">Download</a>  [{$row['dcounter']}]</td>
	
	<td align="left" width="20%"><img src={$row['thumb']}></td>
	<td align="left" width="30%"><b>ID:</b> {$row['id']}<br>
    <b>Title:</b> {$row['title']}<br>
    <b>Artist:</b> {$row['artist']}<br>
	<b>Date:</b> {$row['date']}<br>
	<a href="../../Downloads/Textures.php?id=<?php ['id']">Download</a>  [{$row['dcounter']}]</td>
</tr>
</font>
HTML;
}

Thank you again for your help. I am definatelly gonna name you on my reference page! (if you'd like of course)
 
Upvote 0 Downvote
Code: 
<table width="100%" border="1" rules="groups" <table class="table">

the above does not make sense. why do you have a table tag inside a table tag?

and i do not think that you are following the code posted above too carefully. the reason why the items repeat themselves is that you are not retrieving a database record when moving to the second column.

Code: 
while ($row = mysql_fetch_assoc($result)){
    echo <<<HTML

<tr>
    <td align="left" width="20%"><img src={$row['thumb']}></td>
    <td align="left" width="30%"><b>ID:</b> {$row['id']}<br>
    <b>Title:</b> {$row['title']}<br>
    <b>Artist:</b> {$row['artist']}<br>
    <b>Date:</b> {$row['date']}<br>
    <a href="../../Downloads/Textures.php?id=<?php ['id']">Download</a>  [{$row['dcounter']}]</td>
HTML;
    $row = mysql_fetch_assoc($result);
    if (!$row){
      echo "<td>&nbsp;</td><td>&nbsp;</td>";
    } else {
    echo <<<HTML
    <td align="left" width="20%"><img src={$row['thumb']}></td>
    <td align="left" width="30%"><b>ID:</b> {$row['id']}<br>
    <b>Title:</b> {$row['title']}<br>
    <b>Artist:</b> {$row['artist']}<br>
    <b>Date:</b> {$row['date']}<br>
    <a href="../../Downloads/Textures.php?id=<?php ['id']">Download</a>  [{$row['dcounter']}]</td>
HTML;
} //end if
   echo "</tr>";
} //end while
 
Upvote 0 Downvote
Thx! I had already deleted the second '<' from the first code, that wasn't supposed to be there lol.

Awesome, that works! I also already fixed the download code and now I've got one question left. Is it easy to make it that the last ID's show on the first page and the first ID's show on the last page? With the same code as above?
 
Upvote 0 Downvote
in the paging code i provided there is a first, previous, next, last link automatically coded. the first and previous links do not show if you are on page 1. likewise last and next do not show on the last page.
 
Upvote 0 Downvote
Yes I know, but that's not what I meant. Right now, if you're on the first page, with 10 rows, you are viewing the first 20 ID's. On the second page, as there are only 27 items in the table, you will view the items 21 - 27. But what I meant, was if it was possible to view the items 27 to 8 on the first page and the remaining items, 7 to 1, on the second page.

Meaning that if we'd have 60 items in the table, items 60 - 41 will show on the first, items 40 - 21 on the second and the first 20 items on the third and last page. Do you know what I mean? Do you know what to edit to get that?
 
Upvote 0 Downvote
add an 'order by' clause to your query

Code: 
... ORDER BY id DESC
 
Upvote 0 Downvote
Oh! Yeah indeed, I knew the line, but I actually thought it wouldn't be that simple, but I guess it is! :)

It works completely and just the way I want it! Really really thanx for your great and patient help!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
283
EdLentz
EdLentz
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
250
SashaBuilder3
SashaBuilder3
EdLentz
  • Locked
  • Question
Partial link in email
Replies
1
Views
140
spamjim
spamjim
PeDa
  • Locked
  • Question
Unwanted spaces in php HTML mail 1
Replies
4
Views
233
PeDa
PeDa
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
306
ersatz
ersatz

Part and Inventory Search

Sponsor

Back
Top