PHP and mySQL

[nikomou]

Hey,

I want to import data from a mySQL database, its just simple a small image ($imageurl) with text ($linktext) underneither, and both linking ($url) to a specific page, the problem here is that i want the results in a table with a max of 6 colums and as many rows needed, and i havent got a clue where to start!! I was told i needed a loop or something?

Does anyone know how to do this?

Cheers.

[Twey]

// Vars - fill these in
$address = "localhost"; // The address of your database server.  Leave as "localhost" if it's on the same machine, otherwise put the IP or domain name of the machine it's on.
$username = "yourDBusername"; // MySQL username
$password = "yourDBpassword"; // MySQL password
$db = "database1"; // The database to use
$table = "table1"; // The table your data is in
$imageurlname = "imageurl"; // The name of the field containing the image URLs
$linktextname = "linktext"; // The name of the field containing the link texts
$urlname = "url"; // The name of the field containing the URLs

// Connect to the server
$cn = mysql_connect($address, $username, $password);
// Select database
$rs = mysql_select_db($db, $conn);
// Create the query
$sql = "select * from $table limit 6;"; // limit 6 prevents more than six rows being returned
// Execute the query
$rs = mysql_query($sql, $conn);
// Write data
while($row = mysql_fetch_array($rs)) {
  echo("<a href=\"" . $row[$urlname] . "\">");
  echo("<img src=\"" . $row[$imageurlname] . "\"/>");
  echo("<br/>" . $row[$linktextname] . "</a>");
}
?> 

I hope I've understood you correctly. I don't quite understand how you can have the results in a table with six columns, when there are only three fields and two have to be adjacent underneath the third. So, I just echoed them. I'm sure you can work out how to rearrange the output if I got you wrong.

[nikomou]

Hi,

The code bellow is what i have so far. This pulls up the content fine from the database, all i want to do now, is to place the image with the link bellow, in a table. I want to table to have 6 columns and as many rows needed.

ie, i want to fill up the table with the content from the database in the order:
column 1 - row 1,
column 2 - row 1,
column 3 - row 1,
column 4 - row 1,
column 5 - row 1,
column 6 - row 1, // 6 columns filled up, so go to next row...
column 1 - row 2,
column 2 - row 2,
column 3 - row 2,

ect...............

does that make more sence??

This is the code i have so far, it just displays the data in rows..

<?php

$tariff = $_GET['tariff'];
$make = $_GET['make'];
if($_GET['make'] == "") $make = ("xxxx");
$username="xxxxx";
$password="xxxxx";
$database="xxxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM xxxxx WHERE make='$make'";
$result=mysql_query($query);
$num=mysql_numrows($result);

$handset=mysql_result($result,$i,"handset");
$make=mysql_result($result,$i,"make");
$id=mysql_result($result,$i,"id");

$handsetclean = strtolower($handset);
$makeclean = strtolower($make);
$makeclean2 = str_replace(" ", "-", $makeclean);
$url = str_replace(" ", "-", $handsetclean);
$i=0;
while ($i < $num) {
$handset=mysql_result($result,$i,"handset");
$make=mysql_result($result,$i,"make");
$id=mysql_result($result,$i,"id");
?>
                <span style="FONT-WEIGHT: 700; COLOR: #6491d2; TEXT-DECORATION: none">
                <table cellSpacing="5" cellPadding="10" width="100%" border="0" id="table3">
                    <tr vAlign="center" align="middle">
                        <td bgColor="#ffffff" style="font-family: tahoma; font-size: 11px; text-decoration: none; color: #6491d2" bordercolor="#FFFFFF">
                        <font face="Tahoma" color="#2d75d7" size="2">
                        <a href="http://www.mysite.com/<?php echo ("$makeclean-mobile-phones/$url.php")?>"><font color="#2D75D7">
                        <span style="text-decoration: none; font-weight: 700">
                        <?php echo ("$handset")?></span></font></a></font><font face="Tahoma" size="1"><p>
                        
                        <td width="65" bgColor="#ffffff" style="font-family: tahoma; font-size: 11px; text-decoration: none; color: #6491d2">
                        <p align="center"><font color="#2d75d7">
                        <img src="http://www.affiliatesite.com/images/075/med<?php echo ("$id")?>.jpg" border="0"></font></td>
                    </tr>

<?
$i++;
}

?>

[Twey]

Ah, yes it does. Try this:

// Vars - fill these in
$address = "localhost"; // The address of your database server.  Leave as "localhost" if it's on the same machine, otherwise put the IP or domain name of the machine it's on.
$username = "yourDBusername"; // MySQL username
$password = "yourDBpassword"; // MySQL password
$db = "database1"; // The database to use
$table = "table1"; // The table your data is in
$imageurlname = "imageurl"; // The name of the field containing the image URLs
$linktextname = "linktext"; // The name of the field containing the link texts
$urlname = "url"; // The name of the field containing the URLs

// Connect to the server
$cn = mysql_connect($address, $username, $password);
// Select database
$rs = mysql_select_db($db, $conn);
// Create the query
$sql = "select * from $table limit 6;"; // limit 6 prevents more than six rows being returned
// Execute the query
$rs = mysql_query($sql, $conn);
$j = 0;
// Write data
while($row = mysql_fetch_array($rs)) {
  echo("<td>");
  echo("<a href=\"" . $row[$urlname] . "\">");
  echo("<img src=\"" . $row[$imageurlname] . "\"/>");
  echo("<br/>" . $row[$linktextname] . "</a>");
  echo("</td>");
  $j++;
  if(($j % 6) == 0) echo "</tr><tr>";
}
?> 

[nikomou]

Thanks for that Twey, the code limits the amount of data pulled up from the database!

I would like to show the data in a table. and move on the a new column each time.. untill their are 6 columns...

Then

Start a new row, again, showing the data on the a new column each time untill there are 6 columns!!

e.g.

<table>
	<tr>
		<td>image and link 1</td>
		<td>image and link 2</td>
		<td>image and link 3</td>
		<td>image and link 4</td>
		<td>image and link 5</td>
		<td>image and link 6</td>
	</tr>
	<tr>
		<td>image and link 7</td>
		<td>image and link 8</td>
		<td>image and link 9</td>
		<td>image and link 10</td>
		<td>image and link 11</td>
		<td>image and link 12</td>
	</tr>
</table>

[Twey]

D'oh. Forgot to remove the "limit 6" from the SQL query.

<?php
// Vars - fill these in
$address = "localhost"; // The address of your database server.  Leave as "localhost" if it's on the same machine, otherwise put the IP or domain name of the machine it's on.
$username = "yourDBusername"; // MySQL username
$password = "yourDBpassword"; // MySQL password
$db = "database1"; // The database to use
$table = "table1"; // The table your data is in
$imageurlname = "imageurl"; // The name of the field containing the image URLs
$linktextname = "linktext"; // The name of the field containing the link texts
$urlname = "url"; // The name of the field containing the URLs

// Connect to the server
$cn = mysql_connect($address, $username, $password);
// Select database
$rs = mysql_select_db($db, $conn);
// Create the query
$sql = "select * from $table;";
// Execute the query
$rs = mysql_query($sql, $conn);
$j = 0;
// Write data
echo("<table><tr>");
while($row = mysql_fetch_array($rs)) {
  echo("<td>");
  echo("<a href=\"" . $row[$urlname] . "\">");
  echo("<img src=\"" . $row[$imageurlname] . "\"/>");
  echo("<br/>" . $row[$linktextname] . "</a>");
  echo("</td>");
  $j++;
  if(($j % 6) == 0) echo("</tr>\n<tr>");
}
echo("</tr></table>");
?>

[nikomou]

Thank you sooooo much!! it needed a bit of changing, but works great!!

Do you know if its possible to add something like

"SELECT * FROM $table WHERE make='$make' and date DOESNT EQUAL TO '999'"

[Twey]

SELECT * FROM $table WHERE make='$make' AND date != 999

[nikomou]

Thanks for that. What other commands can you use to filter down results?

SELECT * FROM $table WHERE make='$make' AND date < '999' ?

can you also sort by date?

[Twey]

Yup, that's OK too, along with most of the standard operators. But be careful with those quotes: just like in most languages, quotes denote a string. You can use the "ORDER BY <field>" keyphrase to order the results by <field>, so long as <field> is a MySQL-recognized number or time/date format.