Passing an ID that has yet to be created

herschen

I have a few silly problems. I'm creating a real estate website and I'm adding a feature so the real estate agent can add listings. So he can add as many images as he likes, I've created an images database with only three columns: a Primary Key (which only serves as a placeholder), the PropertyID (the property it's associated with), and the link to where the images are located. When you click on a link for a property's photos, it adds a URL parameter with the PropertyID of that property and then on the following page, I've created a repeating region that only shows the photos associated with that PropertyID. This means that if he wants to add 200 photos for one of his listings he can.

Now here's my problem. I'm creating the feature to add a new listing and the main property page will auto increment to add the new number to PropertyID. How do I pass this number which has not been created yet to another page, so that it can be added to the images database?

benracer

So are you trying to add photos that are related to current property that is allready inserted into a database?

Piranha

herschen wrote:
How do I pass this number which has not been created yet to another page, so that it can be added to the images database?

You have answered your question yourself here. It is not created yet. It means that you don't know what number it will be when it is created. So you can't use it in any way. It seems to me that you have a design fault in your code and/or database if you need it before it is created, you have to work out another way to handle this.

herschen

I hastily wrote this in a flurry of confusion. My apologies. Let me clarify. The first form I created inserts a new row to the 'properties' table with a new description, title, address, price, etc (everything but images). This form is processed, the data is inserted and the user is redirected to a new page, where they upload the first five photographs to the server, and the location of these files is inserted in the 'ImagesMain' table. On this page there is also a form element that asks them how many additional photographs they will add along with the first five--this is passed to the next page.

This last page has a form that allows the user to upload the amount of images they specified in the last page. These images are uploaded to the server and the location of these files are inserted in the 'images' table. However the 'images' database has multiple files with the same primary key from the 'properties' table, so I cannot just autoincrement this number. I don't want the second 'ImagesMain' table to autoincrement the Primary Key because there may come a time when someone wants to add a listing with no images. So I want to have the autoincrementing number from the first page passed to the second and third page and I do not know how.

FYI I created the 'images' table this way because originally, I added 30 images into a web page that got their link from this table, and found that when I had less than 30 images in the database, the web page indicated that it had a missing image. Also, there may come a time when I will need to have more than 30 images. So now, if I have 10 images for property '1,' in the 'images' table, these images all have the PropertyID '1'. If there is a better way to do this, please let me know.

I've picked up a PHP, MySQL, Apache book, so hopefully I'll be on the other end of this forum soon. I really appreciate the help I've received here.

Piranha

That clears things up a bit. You are unfortunatley on the wrong track. Something like this should work:

[b]Properties             Images[/b]
id                     id                    Both these are auto-incremented PK
Street...              propertiesid            propertiesid is a FK to id in properties
...                    path
                       picorder

The picorder column is in what order you want the photos. When you want to get the images for property 1 a query like the one below should work:

SELECT columns FROM images WHERE propertiesid = 1 ORDER BY picorder

herschen

I am using MYISAM, which supposedly does not have support for foreign keys. Is there another option? And how do I specify in the second and third page (after I've already created a new row on the first page) to use the same number that was the autoincrementing Primary Key for the 'properties' table?

Just to clarify: picorder would be good so I can have one images table and then choose which images are going to be the five main images (and the order of the rest)? Thanks for your time.

Piranha

I haven't found anything about it not supporting foreign keys, please provide a manual link with that information. Since foreign keys is a key consept of RDBMS it would be very strange if this is indeed true.

Yes, that was what I ment with picorder.

[man]mysql_insert_id[/man] will allow you to catch the id. Then just pass it on as a GET or POST variable.

laserlight

According to MySQL 5 Manual on Foreign Keys, "For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it."

I am not sure if parsing of foreign key syntax can be considered support for foreign keys, but what Piranha works either way.

herschen

laserlight wrote:
According to MySQL 5 Manual on Foreign Keys, "For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it."

I am not sure if parsing of foreign key syntax can be considered support for foreign keys, but what Piranha works either way.

I've converted my tables to InnoDB. What the information from the manual means is that MySQL 5 will read data from a "Foreign Key" but will ignore all the "Foreign Key" information. I don't plan to come back to PHPBuilder.com until I've read my book thoroughly. That way there will be less chance that I'll ask a question that I already have the answer to.

rsmith

I had to do something similar with a script I wrote. The script would add a new customer and then redirect you instantly to that customers page without knowing what the ID was going to be.

//Store the information into the customers table of the database
		$query = "INSERT INTO customers SET
											fname = '".$fname."',
											lname = '".$lname."',
											address = '".$address."',
											city = '".$city."',
											state = '".$state."',
											zip = '".$zip."',
											category = '".$category."',
											business = '".$business."',
											hphone = '".$hphone."',
											wphone = '".$wphone."',
											mphone = '".$mphone."',
											email = '".$email."',
											centered = '".$centered."'";

	$result = mysql_query($query);
	$cid = ($result and mysql_affected_rows()) ? mysql_insert_id() : 0;

//Redirect to customer's summary page
	header("Location: view-customer.php?cid=$cid");

This part:

$cid = ($result and mysql_affected_rows()) ? mysql_insert_id() : 0;

Gets the recently added row and I just pass it to the header at the end of the script to redirect me to the newly created page without knowing the ID in advance.

Hope this helps.