Printable View
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.
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.PHP Code:// 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>");
}
?>
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 Code:<?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++;
}
?>
Ah, yes it does. Try this:
PHP Code:// 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>";
}
?>
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.
HTML Code:<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>
D'oh. Forgot to remove the "limit 6" from the SQL query.
PHP Code:<?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>");
?>
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'"
Code:SELECT * FROM $table WHERE make='$make' AND date != 999
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?
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.
Cheers for that.
Is it possible to also narrow down the search if a field contains something?
e.g. there's features field has the values basfg, would i be able to just show the deals/data that have an "a" in that field? so it would just show smartphones?
also how would i do the same using the a usual php command?
e.g. if $feature CONTAINS a then .....
(b = bluetooth
a = smartphone
g = 3g
c = camera
s = slider
f = flip
n = normal/candybar
m = mp3
v = video)
I daresay it's possible, though my knowledge of MySQL doesn't stretch that far. :) In PHP you'd use the strstr() function, or stristr() for case-insensitivity. If you want to use regex, you can use preg_match().
If we're still discussing SQL here, one can use the LIKE string comparison operator. This allows values to be matched against a simple pattern. MySQL also has a regular expression syntax...Quote:
Originally Posted by nikomou
...but that would be overkill for this situation. Include a WHERE clause in the SELECT statement along the lines of:Quote:
e.g. there's features field has the values basfg, would i be able to just show the deals/data that have an "a" in that field? so it would just show smartphones?
This would return any tuple that contains an 'a' somewhere in its columnName attribute.Code:SELECT ... FROM ... WHERE columnName LIKE '%a%'
I'd use strpos (or stripos), which is quicker than strstr. It's the equivalent of indexOf methods in languages like Java and ECMAScript.Quote:
also how would i do the same using the a usual php command?
That said, one wouldn't do this using PHP. The database will manage it much better.
Mike
it doesnt seem to like this:
are there any alternatives?Code:SELECT * FROM $table WHERE features LIKE '%$feature%'
You need to close that single quote.
Sorry, my bad - a simple copy and paste mistake there...
That isn't very helpful. What doesn't it like? Exactly what are you sending to the database server?Quote:
Originally Posted by nikomou
I can assure you that used properly, it will work.
Mike
I sorted that out, I accidently opened the php page in frontpage, and it messed up the code - briliant. :eek:
When nothing is found in the sql database, could you set a defult value?!? Instead of recieving them horrible error thingies!!!
Code:$username="xxxx";
$password="xxxx";
$database="xxxx";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM $table WHERE handset='$hsnew'";
$result=mysql_query($query);
mysql_query($query);
$handset=mysql_result($result,$i,"handset");
$make=mysql_result($result,$i,"make");
$id=mysql_result($result,$i,"id");
$desc=mysql_result($result,$i,"description");
$handsetclean = strtolower($handset);
$makeclean = strtolower($make);
$makeclean = str_replace(" ", "-", $makeclean);
$url = str_replace(" ", "-", $handsetclean);
mysql_close();
You can prefix a function with @ to suppress error reporting. In this case,You must, however, include error checking:Code:@mysql_query($query);
Code:$result=@mysql_query($query);
if(!$result) {
$handset="default handset";
$make="default make";
$id="default ID";
$desc="default description"
} else {
$handset=mysql_result($result,$i,"handset");
$make=mysql_result($result,$i,"make");
$id=mysql_result($result,$i,"id");
$desc=mysql_result($result,$i,"description");
}
$handsetclean = strtolower($handset);
$makeclean = strtolower($make);
$makeclean = str_replace(" ", "-", $makeclean);
$url = str_replace(" ", "-", $handsetclean);
using php and MySql, I am using PHP to call into place data groups one at a time. I want to put in next and back buttons for users to see the next group of information or the prev. I am having trouble with this, and wondering if you twey could help. you seem in this thread to understand mixing php and SQL well. please go to
http://uorf.org/new/sponsor.php
if you hit the refresh button a new child is called in. I would like to activate the next button to call the next child ID into place. not sure how this is done. and then the same with the prev button, to display the ID -1. Thank you for your help, I added it to this thread because it was dealing with a similar topic of PHP and SQL working and playing well together.