Output all Image URLs from MySQL using PHP

Report · Jul 29, 2012

I am trying to display all image urls for products in a Mysql table under the columun name 'Photolocation'. Each row can have up to 16 pictures per item seperated by commas. They now cut off at the first photo.

How do I make the code display them all as images?

Photolocation <--- MySql Column Location:

http://images.reinsightdata.com/gamls/l/images/03069544_GAMLS.jpg,http://images.reinsightdata.com/ga...

--------------------------------------------------------------------------------------------------------------------------------------

Php Code:

<?php require_once('Connections/Practice.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

mysql_select_db($database_Practice, $Practice);

$query_photo = "SELECT Photolocation FROM fayettevillemaster";

$photo = mysql_query($query_photo, $Practice) or die(mysql_error());

$row_photo = mysql_fetch_assoc($photo);

$totalRows_photo = mysql_num_rows($photo);

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Untitled Document</title>

</head>

<body>

<img name="" src="<?php echo $row_photo['Photolocation']; ?>" width="350" height="350" alt="">

<?php

mysql_free_result($photo);

?>

</body>

</html>

Report · Jul 29, 2012

>Each row can have up to 16 pictures per item seperated by commas.

That's a poor database design decision. Each row should only contain one url. Use another table to store multiple rows of image location data for each item.

As an alternative, you could probably achieve what you want using the PHP explode function, but you would need to code that by hand. But I would suggest fixing the database design instead.

Report · Jul 29, 2012

OK I will try that. That is how the data was imported to me in a spreadsheet. There should be an efficient way to open the spreadsheet and put the image urls in their own rows, cause it is to much data to do it maunally.

Thanks

Report · Jul 29, 2012

I am assuming that the list of URLs shown is only a partial and there are many more in multiple cells in the spreadsheet. One possible way to convert from SS is to copy the SS cells containing the URLs to a word processor (it should come into the WP as a table), convert the table to text, using a 'replace all', change the .jpg, (.jpg<comma>) with .jpg<paragraph mark>; this will result in a column of URLs. Copy (or import) the column of URLs into a table in your DB. Don't forget you'll need a field in this database linking each URL to it's corresponding record in the main table.

Regards,

TJS

Report · Jul 30, 2012

I ending up using Excel, Text-to-Column feature under the Data Tab. Thanks.

Output all Image URLs from MySQL using PHP

1 Correct answer