noob mysql problem with multiple records

[puremetal]

Hi all, I'm pretty new to mysql and php - I've been familiar with both in using CMS and other apps for a while now, but I haven't yet had the need to work directly in either, bar some minor changes to existing stuff.

I'm trying to create a bridge between two components in Joomla - specifically, a directory listing component and a mapping component. Working in MySQL Query Browser, I've got a query to take the fields I want from the directory listing's data table and insert them into a new row in the map component's placemarks table. I figure I can wipe the placemarks table every 24 hours and re-run the query with cron to copy over any changes that might have been made by users. The idea is to allow users to change their map details (location, description, etc) from within their directory listing.

What I'm having trouble with is that I have a query that works fine when there's one record in the directory listing table. But once there's more than one listing, I get an error saying "Subquery returns more than one row".... which is to be expected, considering I want the query to work on more than one row. I guess the query is trying to put multiple rows' worth of data from one table into a single row on the other, but I could be wrong, or it doesn't know which set of data to take since there's more than one set.

My query is as follows:

INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,`user`,txt)
VALUES ((SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20),
"62",
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13)
)

jos_google_destinations is the mapping table, with columns as follows:
id, name, address, zipcode, town, catid, lat, long, icon_type, published, txt, picture, assign_routeplanning, ordering, user

jos_sobi2_fields_data is the directory listing table with columns as follows:
id, fieldid, data_txt, data_bool, data_int, data_float, data_char, itemid, expiration

To complicate matters, the fields_data table has a weird structure, whereby data is stored in the data_txt column based on the fieldid (eg where the fieldid is 2, the corresponding data_txt in the row is postcode/zipcode)

A copy of the a single record in the directory listing is as follows

1, 14, '56.84499', , 0, 0, '', 1(**ITEMID**), ''
2, 15, '-1.505371', , 0, 0, '', 1, ''
3, 1, 'Street', , 0, 0, '', 1, ''
4, 2, 'Postcode', , 0, 0, '', 1, ''
5, 3, 'City', , 0, 0, '', 1, ''
6, 4, 'County', , 0, 0, '', 1, ''
7, 5, 'State', , 0, 0, '', 1, ''
8, 6, 'UK', , 0, 0, '', 1, ''
9, 7, 'Email', , 0, 0, '', 1, ''
10, 8, 'http://Website', , 0, 0, '', 1, ''
11, 9, 'Contact person', , 0, 0, '', 1, ''
12, 10, 'Phone', , 0, 0, '', 1, ''
13, 11, 'Fax', , 0, 0, '', 1, ''
14, 12, 'Hotline', , 0, 0, '', 1, ''
15, 13, '<p>This is the description field</p>', , 0, 0, '', 1, ''
16, 16, 'My SOBI entry', , 0, 0, '', 1, ''
17, 17, '1', , 0, 0, '', 1, ''
18, 18, 'icon.png', , 0, 0, '', 1, ''
25, 20, '99', , 0, 0, '', 1, ''
32, 19, 'crmember', , 0, 0, '', 1, ''
33, 19, 'fmktloc', , 0, 0, '', 1, ''

the next record continues from id 34...

34, 14, '57.84499', , 0, 0, '', 2(**ITEMID**), ''
35, 15, '2.505371', , 0, 0, '', 2, ''
36, 16, 'Another listing', , 0, 0, '', 2, ''
37, 17, '2', , 0, 0, '', 2, ''
38, 18, 'icon2.png', , 0, 0, '', 2, ''
39, 19, 'crmember', , 0, 0, '', 2, ''
40, 20, '1', , 0, 0, '', 2, ''
etc...

So.... how can I possibly get a query to insert the appropriate data_txt values from one record (itemid 1) to a new row in the google_destinations table, then move on to the next record (itemid2) and do the same again?

Thanks very much indeed for any help, and to anyone who's read this far lol :P

[LifeinAsia]

What I'm having trouble with is that I have a query that works fine when there's one record in the directory listing table.

If it's truly duplicate data (i.e., every field is duplicated, not just some of them), then it should work to use DISTINCT on the select statements (e.g., SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3)). However, it's not going to work if an entry has multiple phone numbers, for example.

You'll need to add it to your WHERE clause on all your selects:
SELECT `data_txt`
FROM jos_sobi2_fields_data
WHERE `fieldid` = 15 AND itemid='1(**ITEMID**)'

You'll need to prgramatically change the value of "1(**ITEMID**)" for each iteration.

Off the top of my head, I think it might be more efficient to redo your system:
* Step 1- add "itemid" field to the jos_google_destinations table

* Step 2- insert basic information for all the data:
INSERT INTO jos_google_destinations (itemid)
SELECT itemid
FROM jos_sobi2_fields_data

If any of the fields are required (non-null) you will have to include those fields in the insert.

* Step 3- loop through all the fields to update the values:
UPDATE jos_google_destinations SET
lat=b.`data_txt`
FROM jos_google_destinations a INNER JOIN jos_sobi2_fields_data b ON a.itemid=b.itemid
WHERE b.`fieldid` = 14

You'll need 1 UPDATE statement for each field. (Also, I am not absolutely sure that syntax will work with MySQL. It does with MS SQL, but you may need to tweak it a bit.)

[puremetal]

thanks LifeinAsia,

i've been trying to programmatically change the value of itemid with php. so far, what i have doesn't work - anybody smarter than me got any ideas why not? :P

i'm getting no errors with the following, but it just doesn't do anything either. the idea is $start is 1, $itemnum is 1++ (incremental increase by 1 from $start), and the WHERE itemid in the query looks at $itemnum for which itemid to use (ie. starting at 1 and working up from there)

<?php 
mysql_connect("localhost", "username", "password") or die(mysql_error()); 
mysql_select_db("databasename") or die(mysql_error()); 
$start = 1;
$itemnum = $start++;
$query = sprintf("INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) 
VALUES 
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = '$itemnum'),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = '$itemnum')
") or die(mysql_error());
$result = mysql_query($query)
?>

if i can't get anywhere with this, i'll try your second idea, LifeinAsia... but i'll be honest and admit that i don't quite understand it. big thanks again for any replies :)

[puremetal]

a quick update: this appears to work in terms of increasing the itemid, as i have it printing what the itemid should be, but the query just isn't running (that's my best guess). this has me very confused!

<?php 
mysql_connect("localhost", "username", "password") or die(mysql_error()); 
mysql_select_db("dbname") or die(mysql_error()); 
$itemnum="0";
do
{
$itemnum++;
echo "The item id for the query is " . $itemnum . "<br />";
sprintf("INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) 
VALUES
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = " . $itemnum . "),
(SELECT DISTINCT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = " . $itemnum . ")
")
or die(mysql_error())
;
}
while ($itemnum<=5)
?>

[LifeinAsia]

Try ECHOing $query and make sure it's exactly what you expect.

[puremetal]

thanks again :)
echo-ing it looks like it should work: (bold just to show itemid 2 as different from 1 and 3)

INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) VALUES ( (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = 1), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = 1) ) ;[B]INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) VALUES ( (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = 2), (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = 2) ) ;[/B]INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) VALUES ( (SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = 3), 
etc...

this works for one record (itemid 1), but not for any others. my guess is that the whole lot is being processed as one query, without seperation between the queries. any ideas what to do here? i've tried putting in a semicolon to seperate the queries, but it doesn't seem to have any effect :-S

<?php 
mysql_connect("localhost", "username", "password") or die(mysql_error()); 
mysql_select_db("dbname") or die(mysql_error()); 
$itemnum="0";
do
{
$itemnum++;
$query = ("(INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) 
VALUES (
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = " . $itemnum . "))
")
or die(mysql_error());
mysql_query ("" . $query . ";");
}
while ($itemnum<=5);
?>

[edited by: puremetal at 5:34 pm (utc) on July 21, 2009]

[puremetal]

update: SOLVED :)

well, it seems solved. the query was being run as a single line string and needed to have a new line per cycle of the script (for each seperate query/itemid)

seems to work now :)

<?php 
mysql_connect("localhost", "username", "password") or die(mysql_error()); 
mysql_select_db("dbname") or die(mysql_error()); 
$itemnum="0";
do
{
$itemnum++;
$query = ("INSERT INTO jos_google_destinations (lat,`long`,name,zipcode,town,catid,icon_type,published,txt) 
VALUES 
(
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 14 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 15 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 16 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 2 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 3 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 17 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 18 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 20 AND `itemid` = " . $itemnum . "),
(SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 13 AND `itemid` = " . $itemnum . ")
)
;")
or die(mysql_error());
//mysql_query ($query);
mysql_query("" . $query . "
");
//mysql_query("" . $query . "/n");
}
while ($itemnum<=5);
?>

[LifeinAsia]

Congrats!