Help with relation between 2 tables using php script

[oalvarado]

Hello All

 

I'm trying to create 2 tables with a relation between them but I keep getting syntax errors, I'm using phpmyadmin for my DB and the script I'm using is in php to create the tables. my code is below and the first table can be created with no issues but the second one is the one with the problem; can someone please help me with that? My code is below.

 

<?php


/* Connect to host & create DB */
// Attempt to connect to MaSQL & print out messages :
if($dbc = @mysql_connect('localhost','root','')) {
print'<p>Successfully Connected to MySQL.</p>';

//Handle the error if the DB could be selected:
if(!@mysql_select_db('realstate')){
print '<p style="color: red;">Could not connect to DB because: <br />'.mysql_error().'.</p>';
mysql_close();
$dbc = FALSE;
}
}else{// Connection Failed
print '<p style="color: red;">Could not connect to MySQL:<br />'.mysql_error().'.</p>';
}

$query = 'CREATE TABLE listados (list_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(20), direccion VARCHAR(40) NOT NULL, colonia VARCHAR(20) NOT NULL, ciudad VARCHAR(30) NOT NULL, estado VARCHAR(20) NOT NULL, pais VARCHAR(20) NOT NULL, zip VARCHAR(10) NOT NULL, email VARCHAR(50) NOT NULL, accion VARCHAR(6) NOT NULL, inmueble VARCHAR(15) NOT NULL, regla1 VARCHAR(3) NOT NULL, superficie VARCHAR(15) NOT NULL, regla2 VARCHAR(3) NOT NULL, construccion VARCHAR(15) NOT NULL, recamaras DECIMAL(2,0), banios DECIMAL(3,1), cubiculos DECIMAL(3,0), moneda VARCHAR(3) NOT NULL, precio DECIMAL(10,2) NOT NULL, description TEXT NOT NULL, fecha DATETIME NOT NULL) ENGINE=InnoDB';

//Execute query
if(@mysql_query($query)){
print '<p> The table has been crated.</p>';
}else{
print '<p style="color:red.">Could not create table because:<br />'.mysql_error().'.</p><p>The query being run was: '.$query.'</p>';
}
$query = 'CREATE TABLE images (img_id BIGINT UNSIGNED NOT NULL, image1 LONGBLOB, image2 LONGBLOB, image3 LONGBLOB, image4 LONGBLOB, image5 LONGBLOB, image6 LONGBLOB, image7 LONGBLOB, image8 LONGBLOB) PRIMARY KEY (list_id) FOREIGN KEY (img_id) REFERENCE images(img_id) ENGINE=InnoDB';
//Execute query
if(@mysql_query($query)){
print '<p> The table has been crated.</p>';
}else{
print '<p style="color:red.">Could not create table because:<br />'.mysql_error().'.</p><p>The query being run was: '.$query.'</p>';
}
mysql_close();//close connection
?>

 

Thanks in advance

[Jessica]

1. Stop using the @ error suppressor. 

2. Why are you creating tables in your PHP script? 

3. What is the mysql error?

4. You need to normalize your data structure. 

5. You really should store images in the filesystem, not in a database.

Edited February 19, 2013 by Jessica

[Barand]

You are allowed to put newlines in queries to make them legible.

 

It's a lot easier to store images on the server and store the name in the db

Edited February 19, 2013 by Barand

[oalvarado]

Thanks for the fast reply Jessica & Barand,

 

The reason I'm using php is because this is going to be my install script once my project is complete and ready to go live. As for my error well its below:

Could not create table because:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY (list_id) FOREIGN KEY (img_id) REFERENCE images(img_id) ENGINE=Inno' at line 1.

The query being run was: CREATE TABLE images (img_id BIGINT UNSIGNED NOT NULL, image1 LONGBLOB, image2 LONGBLOB, image3 LONGBLOB, image4 LONGBLOB, image5 LONGBLOB, image6 LONGBLOB, image7 LONGBLOB, image8 LONGBLOB) PRIMARY KEY (list_id) FOREIGN KEY (img_id) REFERENCE images(img_id) ENGINE=InnoDB

 

 

The whole idea is pretty simple I think, when the form is submitted all info will be inserted in the first table named "listados" that will be the first process since the form allows image uploads the second process will get the id from the first table and the images from the tmp file and insert them into the second table as links to the files where they are located that's why the relation between them. When a user clicks on the listing the info will be displayed as well as a lightbox gallery, that gallery will be fed according to the second table "images" . I hope you guys understand what I'm trying to do.

 

Thanks.

Edited February 19, 2013 by oalvarado

[Jessica]

So it's telling you where the problem is. Look at the mysql syntax. http://dev.mysql.com/doc/refman/5.1/en/create-table.html

 

It looks like you need to move the index definitions inside your parens. 

[oalvarado]

Thanks Jessica that solved my issue.