Lag Time When Accessing Mysql

[Max]

Hi all, and especially Larry,

 

I have encountered an interesting issue when uploading images using a reference from recently uploaded MySQL data. 

 

What I am trying to do is, in one page, send data regarding a property (real estate in US) and an image.  I want the image to have the same reference number as the property.  This is the schematic:...

 

1)  Input html and upload image -> POST to php.

 

2) INSERT property data -> MySQL

 

3) SELECT property reference (Primary key) generated by MySQL.

 

4) Use the property reference + ".jpg" as filename -> the image to a subdirectory on server.

 

The issue is that it seems that MySQL can take a bit too long to do the SELECT query which then throws up a '$image_no undefined variable and the image is saved as '.jpg' instead of e.g. 234.jpg.

 

I think that I have solved it thus (my comments in red): 

 

 

Section of whole program:

 

 

Check if user has sent html POST and uploaded an image (other conditionals not shown here for conciseness):

 

if(isset($_FILES['upload']))

{

....then insert the data into MySQL:

$q = "INSERT INTO db655736144.Properties (Property_name, Owner_name, Owner_phone_number, Owner_email, Description, Electricity, Water, Price, Commission, TCC_Comments) VALUES ('$pty', '$owner', '$phone', '$owner_email', '$desc', '$elec', '$water', '$price', '$comm', '$TCC')";

$r =@mysqli_query ($dbc, $q); //Run the query - could put this in a while loop as well?

$allowed = array ('image/pjpeg', 'image/jpeg', 'image/JPG', 'image/jpg');  

if(in_array($_FILES['upload']['type'], $allowed))  //Check for correct file type

{

$prop_id = "SELECT Property_Ref FROM Properties WHERE Property_name = '$pty'";

//Get property reference number

$prop_id_action = @mysqli_query ($dbc, $prop_id);  //Do query

//$Property_ref is the array containing property ref no generated by MySQL

while ($Property_ref = @mysqli_fetch_array($prop_id_action))

{

$image_no = $Property_ref['Property_Ref'];  //  $image_no is the property ref no generated by MySQL.  This while loop should only run once as Property_Ref is Primary key.

}

If MySQL hasn't done its bit yet, then you are left with $image_no equalling NULL and an error, AND an image called .jpg on the server.  Also the program has to open a new subdirectory with the name Property Ref (e.g. Main/Properties/Adds/123 for additional images to be uploaded to later.  This won't happen.

 

This is the bit ---------------------------------------------------------------------------------------------------

 

I have added a while loop that seems to work here:.....

 

while (!$image_no) //  If the image number from the query = NULL (!$image_no)

{

while ($Property_ref = @mysqli_fetch_array($prop_id_action)) // Repeat the query until we have a value for $image_no

{

$prop_id_action = @mysqli_query ($dbc, $prop_id);  //Do query

$image_no = $Property_ref['Property_Ref'];  //  $image_no is the property ref no generated by MySQL

}

}

--------------------------------------------------------------------------------------------------

 

$_FILES['upload']['name'] = $image_no . '.jpg';  //Add file extension

 

This is just to check that there isn't a subdirectory with the same name (shouldn't be)...

if (move_uploaded_file($_FILES['upload']['tmp_name'], "images/properties/{$_FILES['upload']['name']}"))

{

if(is_dir('images/properties/more_images/' . $image_no))

{

echo "<h2>There was a system problem - please call Max.</h2>";

echo '<p style = "font-weight: bold; font-size: 120%"><a href = "admin.php">Return to menu</a></p>';

die();

}

 

No directory exists so we can contine by creating it:

else

{

mkdir('images/properties/more_images/' . $image_no);

 

Some HTML to return to menu etc:...

echo '<p style = "font-weight: bold; font-size: 120%">File uploaded....<a href = "add_images.php?image_no=' . $image_no . '">Add more images</a>...or...<a href = "admin.php">Return to menu</a>....or just continue.....</p>';

}

....cont

}

else

{

echo "There was a system problem - please call Max.";

die();

}

}

else

{

echo'<p class="emph">Please upload a .jpg image</p>';

}

}

 

 

I'd be very interesed in your comments regarding this 'fix'.

 

Regards

 

Max

 

 

 

 

[Larry]

If property ref is your primary key, just use the mysqli_insert_id() function to retrieve this value instead of running a SELECT query and fetching the results. 

[Max]

That's great, Larry - will reduce server load.  I am a minimalist so love to simplify my software.