Doing php/mysql the right way.

[relientjackson]

I have 2 tables in MySQL.

 

One is a list of shops, which has a ShopID,ShopName,Phone,DisplayAddressID.

Another table is a list of Addresses, with details such as AddressID,AddressLine1,AddressLine2, Suburb, Zip, State etc.

 

I have some php code which gives me the shopid, with a new address to update in the db.

 

Currently to update the address I do the following:

 

$ShopAddrID = mysql_query("SELECT DisplayAddressID FROM shops WHERE ShopID='$shopid'");

$result=mysql_fetch_array($ShopAddrID);

$ShopAddrID = $result[0];

mysql_query("UPDATE addresses SET AddressLine1='$AddressLine1',AddressLine2='$AddressLine2',City='$City',Postcode='$Postcode',State='$State'WHERE AddressID='$ShopAddrID'");

 

 

2 questions:

 

1) If I am pulling only 1 field from a mysql_query, can I reference it easier/simpler? ie. 1 line of code rather than 3.

2) Can I update the address with just one mysql statement?

 

 

I want the code to be efficient and simple.

 

 

Thanks in advance.

 

 

[Fadion]

There's no really a better way of doing it. The only thing I can think of is doing a subqyery in the WHERE clause, which will make the code shorter as you won't have to fetch the array. Take a look at the following code, try it and decide if it's worth it.

 

$shop_id = 1;
$address = 'Some Address';

$results = mysql_query("UPDATE addresses SET address='$address' WHERE id=(SELECT address_id FROM shops WHERE id=$shop_id)");

 

I simplified column names to keep it shorter, but it's basically your 2 queries written in just 1.

[Psycho]

If there is a one-to-one correlation between shops and addresses, why are you storing the addresses in a separate table? You could store it int he shops table and make your life simpler. Or, if you want the data separated, then you should have a foreign key in the addresses table pointing back to the shop record (since the shop is the parent) - not hte other way around as you have it.

 

Anyway, as GuiltyGear stated, using the structure you already have you can simplify it using a subquery. here is the full version:

$query = "UPDATE addresses
          SET AddressLine1='$AddressLine1', AddressLine2='$AddressLine2',
              City='$City', Postcode='$Postcode', State='$State'
          WHERE AddressID = (SELECT DisplayAddressID FROM shops WHERE ShopID='$shopid')";
mysql_query($query);

[relientjackson]

Cheers guys for this, much appreciated.

 

I didn't design the original db, although the shops can have a Display Address and a Postal Address, so I assume that's the reason.

 

As a follow up, do you have any recommended reading for designing databases that scale?

 

Cheers

 

Mark

[Fadion]

I would recommend Art of SQL. It's pretty much of a classic that goes into really deep details about schema and performance. It needs some experience though, but you can get the grasp of lots of stuff while reading it.