SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Dynamic query

  1. #1
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Dynamic query

    I am currently trying out new things, and the first is to try and create a photo album in php and mysql. Now I have got a query that simply adds tables and rows into a database. My problem is, I want these to be dynamic, and dont have to be entered in the source code.

    So here is my code, and below is what I want to do. There is 2 pages of code, one to process, one to send the values.


    Index.php
    HTML Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    
    <body>
    <form id="Create_album" method="post" action="Create_album.php">
      <p>Name: 
        <input name="Name" type="text" id="Name" maxlength="255" />
    </p>
      <p>
        <input name="Date_created" type="hidden" id="Date_created" value="<?php echo date("l dS of F Y h:i:s A"); ?>" />
        <input name="Created_by" type="hidden" id="Created_by" value="<?php echo ucFirst($_SESSION['MM_Username']); ?>" />
    </p>
      <p>
        <input name="Create" type="submit" id="Create" value="Create" />
      </p>
    </form>
    </body>
    </html>

    Create_album.php
    PHP Code:
    <?php 
    // Get Variables

    $name $_POST['Name'];
    $date $_POST['Date_created'];
    $created_by $_POST['Created_by'];



    // Make a MySQL Connection
    mysql_connect("localhost""root""") or die(mysql_error());
    mysql_select_db("Php_photo_album") or die(mysql_error());



    // Create a MySQL table in the selected database
    mysql_query("CREATE TABLE Album_name_information(
    Name VARCHAR(255) NOT NULL, 
    Date_created VARCHAR(255) NOT NULL, 
    Created_by VARCHAR(255) NOT NULL
    )"
    )
    or die(
    mysql_error());

    echo 
    "Table Created!";


    // Create a MySQL table in the selected database
    mysql_query("CREATE TABLE Album_name_images(
    Name VARCHAR(255) NOT NULL, 
    Caption VARCHAR(255) NOT NULL, 
    File_name VARCHAR(255) NOT NULL, 
    Added_by VARCHAR(255) NOT NULL
    )"
    )
    or die(
    mysql_error());

    echo 
    "Table Created!";



    // Insert a row of information into the table
    mysql_query("INSERT INTO Album_name_information 
    (Name, Date_created, Created_by) VALUES('
    $name', '$date_created', '$created_by' ) "
    or die(
    mysql_error()); 

    echo 
    "Data Inserted!";
    ?>

    Well thats my code... it may seem quite stupid to you, and may contain heaps of things you wouldnt dare to that way... but give my a break, im only 13!

    These are the parts troubling me:

    mysql_query("CREATE TABLE Album_name_images(

    AND

    mysql_query("INSERT INTO Album_name_information


    Instead of "Album_name" I need the obtained varible to be printed. I tried having it as:

    mysql_query("CREATE TABLE $name_images(

    but that generated an error "You have an error in your SQL syntax near '( Name VARCHAR(255) NOT NULL, Date_created VARCHAR(255) NOT NULL, Created_b' at line 1".

    Does anyone know how I can do what I am trying to do?

    And another thing, did I post in the right place? Or should have it been php?


    Thankyou in advance

    ~Brent~

  2. #2
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you creating a different table for each album? If so, it would be much easier to have an `albums` table in which you store the album information, and then an images table that has the albums id as a foreign key, like so:

    Code:
    CREATE TABLE `albums`(
       `id` int(11) unsigned NOT NULL auto_increment,
       `Name` varchar(255) NOT NULL default '',
       `Caption` VARCHAR(255) NOT NULL,
       `File_name` VARCHAR(255) NOT NULL,
       `Added_by` VARCHAR(255) NOT NULL,
        PRIMARY KEY  (`id`)
    ) 
    
    CREATE TABLE `images`(
       `id` int(11) unsigned NOT NULL auto_increment,
       `albums_id` int(11) unsigned NOT NULL,
       `Name` VARCHAR(255) NOT NULL,
       `Caption` VARCHAR(255) NOT NULL,
       `File_name` VARCHAR(255) NOT NULL,
       `Added_by` VARCHAR(255) NOT NULL,
        PRIMARY KEY  (`id`) 
    )
    Much more dynamic, much easier to maintain. But, if you still want to create different tables for each album (not recommended), change you CREATE TABLE syntax to this:

    PHP Code:
    mysql_query("CREATE TABLE {$name}_images(...."); 


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
  •