[Wolves] php/mysql wizzards please - sparkes?

[Wayne Morris]

Hi Chris,

The bit where I left a comma out that you pointed out worked!
Thanks very much.
Now I just need to learn joins.

Wayne Morris



On Mon, 2004-03-15 at 10:32, Chris Procter wrote:
> 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.
>