PDA

View Full Version : PHP and mySQL



nikomou
11-06-2005, 11:16 PM
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
11-07-2005, 12:46 PM
// 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
11-07-2005, 04:57 PM
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
11-07-2005, 07:34 PM
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
11-07-2005, 08:52 PM
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
11-08-2005, 09:40 AM
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
11-08-2005, 08:30 PM
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
11-09-2005, 01:02 PM
SELECT * FROM $table WHERE make='$make' AND date != 999

nikomou
11-10-2005, 08:36 AM
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
11-10-2005, 07:50 PM
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.

nikomou
11-10-2005, 10:43 PM
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)

Twey
11-11-2005, 04:27 PM
I daresay it's possible, though my knowledge of MySQL doesn't stretch that far. :) In PHP you'd use the strstr (http://uk.php.net/strstr)() function, or stristr (http://uk.php.net/stristr)() for case-insensitivity. If you want to use regex, you can use preg_match (http://uk.php.net/manual/en/function.preg-match.php)().

mwinter
11-11-2005, 06:37 PM
Is it possible to also narrow down the search if a field contains something?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...


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?...but that would be overkill for this situation. Include a WHERE clause in the SELECT statement along the lines of:



SELECT ... FROM ... WHERE columnName LIKE '%a%'
This would return any tuple that contains an 'a' somewhere in its columnName attribute.


also how would i do the same using the a usual php command?I'd use strpos (http://uk.php.net/strpos/) (or stripos (http://uk.php.net/stripos/)), which is quicker than strstr. It's the equivalent of indexOf methods in languages like Java and ECMAScript.

That said, one wouldn't do this using PHP. The database will manage it much better.

Mike

nikomou
11-12-2005, 02:31 PM
it doesnt seem to like this:


SELECT * FROM $table WHERE features LIKE '%$feature%'

are there any alternatives?

Twey
11-12-2005, 04:45 PM
You need to close that single quote.

nikomou
11-12-2005, 04:48 PM
Sorry, my bad - a simple copy and paste mistake there...

mwinter
11-13-2005, 12:13 AM
it doesnt seem to like this:


SELECT * FROM $table WHERE features LIKE '%$feature%'That isn't very helpful. What doesn't it like? Exactly what are you sending to the database server?

I can assure you that used properly, it will work.

Mike

nikomou
11-14-2005, 05:22 PM
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!!!




$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();

Twey
11-14-2005, 06:20 PM
You can prefix a function with @ to suppress error reporting. In this case,
@mysql_query($query);You must, however, include error checking:
$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);

pkcidstudio
02-09-2007, 07:52 PM
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.