4 Replies Latest reply: Jul 30, 2012 1:18 PM by mercer1022 RSS

    Output all Image URLs from MySQL using  PHP

    mercer1022

      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.reinsightd ata.com/gamls/l/addl_picts/03069544_GAMLS-1.jpg,http://images.reinsightdata.com/gamls/l/ad dl_picts/03069544_GAMLS-2.jpg,http://images.reinsightdata.com/gamls/l/addl_picts/03069544_ GAMLS-3.jpg,http://images.reinsightdata.com/gamls/l/addl_picts/03069544_GAMLS-4.jpg,http:/ /images.reinsightdata.com/gamls/l/addl_picts/03069544_GAMLS-5.jpg,http://images.reinsightd ata.com/gamls/l/addl_picts/03069544_GAMLS-6.jpg

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

       

      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>

      <meta http-equiv="Con=96+-ptent-Type" content="text/html; charset=utf-8">

      <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>

        • 1. Re: Output all Image URLs from MySQL using  PHP
          bregent CommunityMVP

          >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.

          • 2. Re: Output all Image URLs from MySQL using  PHP
            mercer1022 Community Member

            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

            • 3. Re: Output all Image URLs from MySQL using  PHP
              TjmSmith Community Member

              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

              • 4. Re: Output all Image URLs from MySQL using  PHP
                mercer1022 Community Member

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