How to query a dynamically generated mysql table name?

[seniramsu]

This concerns php as well as mysql.

 

I'm setting up a photo gallery management page similar to, for example, facebook -- where the client will be able to manage his site's photo gallery.

 

Basic functions of the Admin Albums section:

[*]Give an album a name

[*]Create the album (both of these use an html form, see below)

 

<form method="post" action="adminAlbums.php" id="createAlbum_form">
          <label>Album Title:  <input type="text" name="albumTitle" id="albumTitle" /></label>
          <input type="submit" name="createAlbum" id="createAlbum" value="+ Create New Album" />
</form>

 

if (isset($_POST['albumTitle'])) {
$albumTitle = $_POST['albumTitle'];
$query = "CREATE TABLE `ridgeline`.`" . $albumTitle . "` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL ,
`type` VARCHAR( 50 ) NOT NULL ,
`size` INT( 11 ) NOT NULL ,
`content` LONGBLOB NOT NULL ,
`title` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ";
mysql_query($query, $db_server) or die("<p style=\"color:#f00;\">Query Failed. Please enter a name for this album</p>");
echo "<p>An album called \"" . $albumTitle . "\" has been created!</p>" ;

 

So the text input within the html form is assigned to a php variable, which is then passed into the mysql query to be used to name the table.

 

Then, I will create a new HTML table on the admin page which contains:

[*]thumbnail of the album

[*]the *album name* (which is being grabbed, at the moment, through a $_POST global, versus actually being queried from the mysql database)

[*]a rename function (renames the name displayed for the album on the page, as well as the name of the table in the database, simultaneously)

[*]and several admin controls (promote/demote, show, delete).

 

Here's the code for this so far (pardon the ugly formatting):

if(isset($_POST['albumTitle']) && isset($_POST['createAlbum'])){
$albumTitle = $_POST['albumTitle'];
} elseif(!isset($_POST['albumTitle'])) {
$albumTitle = "Generic Title";
}

$newAlbum =
"<table id=\"adminAlbums\">
<tr>
        <td id=\"thumbnailContainer\"></td>
        <td id=\"albumName\"><h2>" . $albumTitle . "</h2>
</td>
<td id=\"albumThumbnail_control\">
            <form name=\"albumThumbnail_control\" enctype=\"multipart/form-data\" action=\"adminAlbums.php\" method=\"POST\">
<input name=\"renameAlbum\" id=\"renameAlbum\" type=\"text\" />
                <input name=\"renameAlbumSubmit\" id=\"renameAlbumSubmit\" type=\"submit\" value=\"Rename\" />
            </form>
        </td>     

        <td id=\"adminAlbumsControl\">
          <form name=\"control\" id=\"control\" enctype=\"multipart/form-data\" action=\"adminAlbums.php\" method=\"POST\">
<label id=\"checkbox\" ><input type=\"checkbox\" value=\"\" checked=\"true\" /> Show</label>    
                <input name=\"moveUp\" type=\"button\" value=\"Move Up\" />
                <input name=\"moveDown\" type=\"button\" value=\"Move Down\" />    
            <input name=\"delete\" type=\"button\" value=\"Delete\" />
            </form>
        </td>
</tr>
</table>";

if(isset($_POST['albumTitle'])) {
echo $newAlbum;
}

 

 

MY QUESTION:

How to I access the MYSQL TABLE NAME in my query? There used to be an actual query for this (mysql_tablename) which is now deprecated, and php.net suggests to use simply mysql_query. Great, but I've been scouring books and the internet to no avail. I need to access the mysql table name directly, but since it's dynamically generated, I can't query a specific name. The closest thing I can do is to access the name through the $_POST[], however, this will only enable me to access the most recent data posted through the text form.

 

Here's an example of what I tried. I'm sure it's full of holes.

$query = "SELECT * FROM ridgeline";
$result = mysql_query($query, $db_server);
$row = mysql_fetch_assoc($result);
echo $row[''];

I've asked some programming friends. I've searched forum after forum. Looked at php.net and mysql sites. I've trialed and erred...And still haven't really found what I'm looking for. Maybe I'm not looking in the right place?

 

Can someone point me in the right direction? Thanks in advance. 

 

 

[trq]

I'm not sure the idea of creating a new table for each album is the best design in the first place. Any particular reason you have taken this approach?

[seniramsu]

I'm not sure the idea of creating a new table for each album is the best design in the first place. Any particular reason you have taken this approach?

 

if you mean mysql table...

I decided to do this so that in creating the 'album', the client is actually just creating a database table to be used to store all of the pictures he will upload for a particular album.

 

if you mean html table...

I felt it would make it very intuitive for the client (who isn't very computer proficient) to manage his albums, by creating a layout similar to something he has likely used before (like the facebook-style album and picture management pages). It looks better in the actual browser than in code because of the css and what not.

 

In any case, I think I may have found my answer. Here's what I've managed to do so far with the mysql_query.

http://us3.php.net/manual/en/function.mysql-query.php (I should've kept reading!)

 

        $query = "SHOW TABLES FROM `ridgeline` LIKE `picture_upload`";
$result = mysql_query($query, $db_server);

 

Don't know why it didn't occur to me before: I can't access a mysql table name that has been dynamically generated. BUT, with the above function, I can easily create my own 'index' table that has the same model of the SQL tables that will be generated upon the client's input. The above function will find any tables LIKE 'picture_upload'. So, as long as the dynamically generated ones are the same format as my 'picture_upload' (index) table, I'm golden.

 

Still got some work to do on this, but I suppose I'll put up the working script when I get it, so other people with a similar issue can use it. Thanks a lot. 

 

[trq]

if you mean mysql table...

I decided to do this so that in creating the 'album', the client is actually just creating a database table to be used to store all of the pictures he will upload for a particular album.

 

Yeah, I meant database table. I still don't really understand why each album would need its own table.

[DavidAM]

Creating a separate database table for each album is going to be a nightmare for access and for maintenance.  Unless you have some very compelling reason for doing it, I would suggest using two tables:

 

Albums: album_id int auto_increment, owner_id int (foreign key to users table), name varchar

 

Photos: photo_id int auto_increment, album_id int (foreign key to albums table), filename

 

 

[seniramsu]

honestly, I do realize my database is poorly structured, now that you mention it. It will definitely save time and space if I don't give each album a table. Not to mention accessing that data is much more convenient. I appreciate the help. I'm a beginner with this, so I've gotta get up to speed on getting your suggestion to work within the context of my project.

 

I'll keep this thread posted.

 

thanks again!

[seniramsu]

Gentlemen, thanks for the help.

I took your advice on the database restructure -- much much more efficient.

Here's what I came up with. Works like I need it to, now I just need to take the data I've been slaving to retrieve and pretty it up a bit with CSS. 

 

Form to input the name for a new album:

<form method="post" action="adminAlbums.php" id="createAlbum_form">
      	<label>Album Title:  <input type="text" name="albumTitle" id="albumTitle" /></label>
        <input type="submit" name="createAlbum" id="createAlbum" value="+ Create New Album" />
</form>

 

To send the form to the database:

if (isset($_POST['albumTitle'])) {
$albumTitle = $_POST['albumTitle'];

$query = "INSERT INTO `ridgeline`.`albums` (`album_id`, `owner_id`, `name`) VALUES (NULL, '1', '" . $albumTitle . "');";

mysql_query($query, $db_server) or die("<p style=\"color:#f00;\">Query Failed. Either enter a name for this album, or: " . mysql_error() . "</p>");

echo "<p>An album called \"" . $albumTitle . "\" has been created!</p>" ;

 

To access the dynamically-generated album name:

$query = "SELECT * FROM `ridgeline`.`albums` WHERE `album_id` > 0 ORDER BY album_id ASC";

	$result = mysql_query($query, $db_server);

	while(isset($result)) {
		$row = mysql_fetch_assoc($result);

		$id = $row['album_id'];
		$user = $row['owner_id'];
		$name = $row['name'];

		if(isset($id)) {
			echo $id . ", " . $user . ", " . $name . "<br />";
		}
		elseif(!isset($id)) {
			break;
		}
	}

I'm sure there's an even better way to do this, although for now it does the job. Feel free to make any more suggestions, they'd be much appreciated.

 

Cheers!