Help with MySQL query - inner join?

[ramone_johnny]

Hi guys,

I'm trying to pull a value from the database and assign it to a variable, of which I then need to populate another table.

 

My background is ASP/MSSQL so I'm not overly familiar with how to do this.

 

I have the following query, which is erroring out. I need help here.

$r=query_wrapper("SELECT pcode_area, pcode_postcode, pcode_city FROM tblnew_cities WHERE pcode_area = ?",$share_suburb . " AND pcode_postcode = ?",share_postcode);
$rstDBEdit=mysql_fetch_assoc($r);
$a = array();
$a["pcode_city"] = $pcode_city;

echo $pcode_city;
exit();

Then of course once I have the $pcode_city I need to insert it into the tblshare_adverts table. (see below)

$r=query_wrapper("SELECT * FROM tblshare_adverts");
$rstDBEdit=mysql_fetch_assoc($r);
$a = array();
$a["share_number"] = $share_number;
$a["share_street"] = $share_street;
$a["share_suburb"] = $share_suburb;

....etc etc

$a["share_city"] = $pcode_city;

...etc etc

query_wrapper("INSERT INTO tblshare_adverts SET ?%",$a);

$share_ID = mysql_insert_id();

I'm guessing this could be simplified by perhaps using an inner join and running just one query?

 

Or is this way okay?

 

[Ritual29]

I'm not totally sure what you are trying to do.

It looks like you are inserting an array into your table?

 

Is there no other way to do this? I think you can better make separate fields in your table for the values you're inserting.

 

Besides of that, an insert query doesn't work with 'SET', you use this with an update query.

 

the proper syntax is

INSERT INTO table (field1, field2) VALUES (value1, value2)

I don't know if you can use it, but you have to figure out yourself how to use it in your situation then.

You can also use a select query inside the insert query, just google for how to use a subquery.

Edited January 5, 2014 by Ritual29

[mac_gyver]

@Ritual29,the SET syntax is perfectly valid for an INSERT query -

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [iGNORE]
[iNTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

 

[mac_gyver]

I have the following query, which is erroring out.

 

 

it would sure help if you posted that error. if it concerns using an undefined constant, that's because you left off the $ in front of the share_postcode variable in the query statement.

 

---

 

all the logic you have after your mysql_fetch_assoc() statements is invalid. the $rstDBEdit variable contains the fetched array and you would reference individual elements of that array like - $rstDBEdit["pcode_city"].

 

also, php assignment statements assign the value on the right-hand side of the = to the variable on the left-hand side.

 

---

 

you can use an INSERT ... SELECT query to select data from one table and insert it into another table using one query - http://dev.mysql.com/doc/refman/5.6/en/insert-select.html