Results 1 to 6 of 6

Thread: SQL with PHP

  1. #1
    Join Date
    Dec 2009
    Posts
    54
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question SQL with PHP

    Hi Guys,

    I have a MySQL database and have connected to it in my PHP page and I have a PHP gallery with filename as ID numbers on the same page (for example - 123456) [$img] each ID number is unique.

    Is the following possible?

    Get the db to query $img to get that row
    Tell the db to set the field 'name' within that queried record to $id
    Tell the PHP to display $id (this step I can do)

    So I basically want to have the img filename in the gallery as the id number but I want the db to show the name as opposed to their id number (filename) below the image

    Thanks

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Images (files), PHP variables, and Databases are all very separate things.

    We would probably need to see the gallery script you are using if you expect to integrate this with what you are already doing. It sounds like you just want to store captions to go with each image…?

  3. #3
    Join Date
    Dec 2009
    Posts
    54
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    Hi Traq,

    Thanks for replying

    I am new(ish) to PHP and new to MySQL so please let me know if what I am doing is impossible and/or other ways it can be done.

    I am using the gallery Galpop - http://galpop.magicmediamuse.com/

    but I have some PHP code to replace the code that comes with Galpop

    PHP Code:
    <?php
    //include 'db_connect.php';
    ?>

    <?php  
    $filePath 
    'album/clients/';    
    $dir opendir($filePath);  
    while (
    $file readdir($dir)) {   
        if (
    preg_match("/\.(png)|(jpg)|(gif)$/i"$file)) {   
            
    $string[] = $file;  
        }  
    }  
    while (
    sizeof($string) != 0){  
        
    $img array_pop($string);
        
    $strSQL = ("SELECT ID FROM clients WHERE ID = '$img'");
    $id = ($strSQL);

    ?> 
    <div id="img-contain"> 
    <a href="db_connect.php" width="130"><img border=0 src="<?php echo "$filePath$img"?>" width="150" /></a><br /><div id="img-name"><?php echo $id?></div>
    </div>
    <?php  
    }  
    ?>
    Thanks
    Last edited by traq; 04-09-2014 at 10:56 PM. Reason: added [php] tags

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Please use the forum's bbcode tags and indent your code to make it more readable:

    for php code............[php] <?php /* code goes here */ ?> [/php]
    for html...............[html] <!-- markup goes here -->.....[/html]
    for js/css/other.......[code] code goes here................[/code]

    results in:
    PHP Code:
     <?php /* code goes here */ ?>
    HTML Code:
     <!-- markup goes here -->
    Code:
     code goes here


    I you'd like to add a database, you should probably use it to manage the file paths as well, instead of scanning through the directory. For example, you could have a DB table like so:
    Code:
    create table `image_gallery`(
        `path`    varchar(140) primary key,
        `caption` varchar(140)
        -- you could even store info about the image size and alt attribute,
        -- which gallery an image belongs in
        --   (if you want to support multiple galleries),
        -- who "owns" each image and when they were uploaded,
        -- publish/suspend particular images,
        -- or more.
    );
    Then, you'd select all the info from the DB directly. How much do you understand about working with databases in PHP? Do you already have a database set up? have you used database connections in PHP (e.g., PDO)?

  5. #5
    Join Date
    Dec 2009
    Posts
    54
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default

    Sorry about that, will use it next time

    To answer your questions:

    Yes I have a db set up and I don't understand connections at all really as this is my first attempt, so unforch your example means very little to me atm as i don't understand it lol, I have searched and searched but can't find any examples/tutorials for what I want to do

    Thanks

  6. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Alright, no problem.

    Let's start with the database itself. First, you need to know your DB hostname, username, password, and the name of the database itself. You should have all of these from when you set up the database. You'll need them in your script to open a connection to the database (but don't post them here -or anywhere- ******* them out instead).

    Next, you'll need to decide what information you need to store in the database and then create a database table. That's the code I posted above. Let's add another field to differentiate between the large images and the thumbnails; then, we'll need to load all of your records.

    To retrieve that information, you'll use a select statement ("query"). Your query will probably look something like this:
    Code:
    select `path`,`caption` from `image_gallery`
    Before PHP can query the database, it will need to establish a connection to it. There are several choices. I will give you an example using [http://php.net/pdo]PDO[/url], but you could use MySQLi instead. Do not use the mysql_* functions. They are deprecated and are not recommended for new code.
    PHP Code:
    <?php

    // get a PDO object that is connected to your database
    // * if you are running PHP version prior to 5.3.6, we'll have to change this slightly. let me know.
    try{
        
    $PDO = new PDO"mysql:host=your.db.hostname;dbname=your_db_name;charset=utf8","your db username","your db password" );
    }
    catch( 
    PDOException $PDOe ){
        
    /* the connection failed. during development, you can do: */
        // print $PDOe->getMessage();
        /* to see what the problem was.  but _never_ do this on your live site. */
        
    exit( "database connection failed." );
    }
    Once you are connected, you can prepare your query and then send it to the database:
    PHP Code:
    <?php

    try{
        
    $query $PDO->prepare"select `path`,`caption` from `image_gallery`" );
        
    $query->execute();
        
    // loop over results
        
    while( $row $query->fetch() ){
            
    // build your <img> tags for the gallery
            // add a width attribute to the thumbnail image to keep it smaller, or use css.
            // if the images are very large, you might want to add another field to the DB for the thumbnail image.
            
    $img[] = '<a href="http://example.com/'$path .'" class="galpop" data-galpop-group="gallery">'
                
    .'<img src="http://example.com/'$path .'" alt="">'
                
    .'</a>';
        }
        
    // if there are any images, we'll print them:
        
    if( ! empty( $img ) ){
            
    // combine all imgs into markup for gallery
            
    $gallery implode$img,"\n" );
            
    // and print
            
    print $gallery;
            
    // all done  :)
        
    }
    }
    catch( 
    Exception $e ){
        
    /* as above, something went wrong.  try: */
        // print $e->getMessage();
    }

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •