[Wolves] php/mysql wizzards please - sparkes?

[Chris Procter]

Three possibilities,
1)
$qy = "SELECT * FROM counties";
$rs = mysql_query ($qy);
while ($r = mysql_fetch_array ($rs))
 {
 $qu = "UPDATE phonelist SET county = '{$r['county']} WHERE county
={$r['oldid']}";
 mysql_query ($qu);
 }

You are missing a ' after {$r['county']} in the UPDATE statement

2) mySql doesn't like write to fields its searching on, 

3) (probably more likely) you are trying to write a string value (the county
name) to a integer field (meant to hold an id that identifys the county)

If its 2 or 3 you could add a new column to the phonelist table called
countyname with a string datatype (char? nvarchar? something like that) and
change your query to 
UPDATE phonelist SET countyname = '{$r['county']} WHERE county
={$r['oldid']}

Or you could keep them as seperate tables and learn to inner join :-)
SELECT * FROM phonelist INNER JOIN counties ON
phonelist.county=counties.oldid

Assuming counties is just a list of counties each with a numerical id
associated with it (which is used in the phonelist table) you should never
need to update counties (as they dont change very often), you only need to
UPDATE the phonelist with the new county id when somebody moves county, so
no need for JOINs on your UPDATE statement at all

I.e you select the new county from a drop down box

<select name="countyid">
<%
$qy = "SELECT * FROM counties";
$rs = mysql_query ($qy);
while ($r = mysql_fetch_array ($rs))
 {
 echo "<option value={$r['oldid']}>{$r['countyname']}</option>
 }
%>
</select>


and then 
UPDATE phonelist SET county= {$r['countyid']} WHERE person=personid
would move them to a new county.

I've never done much PHP though, so beware. I do do ASP + SQL for a living
though.

chris



-----Original Message-----
From: Wayne Morris [mailto:wayne at machx.co.uk]
Sent: 12 March 2004 18:18
To: Wolverhampton Linux User Group
Subject: [Wolves] php/mysql wizzards please - sparkes?


I've got what should be a really simple operation to do and I just can't
get it, despite hours on google and some posts in the php groups.

All I want to do is this:

I have a contacts database with a table 'phonelist' containing a field
'county' which currently contains numbers referred to in another table
'counties' which contains 'county' and 'oldid'.
I want to get rid of the 'county' table and insert the county names from
it into the 'county' field as a one off.

So i've tried all sorts of stuff like this on the mysql prompt:

Update phonelist.county SET phonelist.county = counties.county WHERE
phonelist.county=counties.oldid

and stuff like this in php

$qy = "SELECT * FROM counties";
$rs = mysql_query ($qy);
while ($r = mysql_fetch_array ($rs))
 {
 $qu = "UPDATE phonelist SET county = '{$r['county']} WHERE county =
{$r['oldid']}";
 mysql_query ($qu);
 }

with no joy.

Anyone tell me what I should be doing?

Cheers.




Wayne Morris
07960 859346
-- 

Live machinery database
www.machx.co.uk/classifieds/
Most recently added items
www.machx.co.uk/classified/recent.php


_______________________________________________
Wolves LUG mailing list
Homepage: http://www.wolveslug.org.uk/
Mailing list: Wolves at mailman.lug.org.uk
Mailing list home: http://mailman.lug.org.uk/mailman/listinfo/wolves


**********************************************************************
Any opinions expressed in this email are those of the individual
and not necessarily those of Fox Online.
This email and any files transmitted with it, including replies and
forwarded copies (which may contain alterations) subsequently transmitted
from Fox Online, are confidential and solely for the use
of the intended recipient.
If you have received this email in error please notify Fox Online by
telephone on +44 (0)121 693 1424.
**********************************************************************