SQL with PHP

[moose86]

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

[traq]

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

[moose86]

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

[traq]

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 goes here */ ?>

 <!-- markup goes here -->

 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:

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

[moose86]

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

[traq]

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:

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

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

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