Modifying an SQL query help!

[stewc]

I have a database in which there is a table 'content' which contains a field called 'address'

 

This line of code pulls some info from the database

 

$query= "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM content AS source, postcodes AS dest WHERE address = dest.outcode GROUP BY dest.outcode ASC";

 

'address' is in the form

 

'1 Street Name<br/ >Address Line1<br />Address Line2<br />Postcode'

 

where postcode is the form 'AB12 3CD'

 

What I want is to use only the first part of the postcode ie AB12 rather than the whole line.

 

This code modifies the address to how I want it

 

$query = "SELECT address FROM content;

$result = @mysql_query ($query);

 

while($row = @mysql_fetch_array($result))

{

$addy = substr(strrchr($row['address'],">"),1,4);

}

 

but how do I incorporate this into the $query at the top?

 

TIA

[hemlata]

Hello,

 

You can write the update query in the while loop. Take an example given below.

while($row = @mysql_fetch_array($result))
{
$addy = substr(strrchr($row['address'],">"),1,4);
$query = mysql_query("UPDATE `content` SET `address` = '".$addy."' WHERE `address` = '".$row['address']."'") or die(mysql_error());
}

 

Hope this could solve your issue.

Regards,

[stewc]

What I'm sayin is I have this line of code

 

$query= "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM content AS source, postcodes AS dest WHERE address = dest.outcode GROUP BY dest.outcode ASC";

 

and want to apply this string modification

 

substr(strrchr($row['address'],">"),1,4)

 

But I cant do this can I

 

$query= "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM content AS source, postcodes AS dest WHERE substr(strrchr($row['address'],">"),1,4) = dest.outcode GROUP BY dest.outcode ASC";

 

cos it throws up an error.

 

 

[fenway]

MySQL has substring() function too....

[stewc]

So should this line

 

$query= "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM content AS source, postcodes AS dest WHERE substr(strrchr($row['address'],">"),1,4) = dest.outcode GROUP BY dest.outcode ASC";

 

work ok or have I got to use substring()?

[fenway]

That won't work because you have php code in your sql statement.  You need to convert that php code into the equivalent sql code with SUBSTRING() and LOCATE().

[stewc]

Can someone explain what this line of code does?

 

I sort of understand it but am confused by the dest.outcode (what is the purpose of the dot) and the variables seperated by the commas?

 

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM hwz_users AS source, hwz_postcodes AS dest WHERE source.outcode = dest.outcode GROUP BY dest.outcode ASC";

 

[fenway]

Varibles? No variables... it's TABLEALIAS.COLUMNNAME.

[stewc]

So my table ALIAS is dest and COLUMNNAME is outcode?

 

Column name makes sense as I have a column in a table called outcode but how do I know what table it refers to?

[fenway]

TABLE AS ALIAS.

 

hwz_postcodes AS dest