Searching database for existing counties, then listing them

Beginners - Learning PHP
#1

OK, so I have a database table which stores member details including addresses. I essentially want to add a list of counties to a form select box, but rather than list all counties and have some return no matches, I want to list only counties that actually exist in the database in the select box.

I may be going about this the wrong way, but thought if I had an array of all the counties and an array of counties in the database I could then use array_intersect to see which counties exist ??

The address details are all stored in one column on the database called memberproperties as such: {“address”:“12 Address Street”,“city”:“City”,“county”:“County”,“post_code”:“MMM 1MM”} so I also assume I need json_decode.

I am getting in a complete pickle as I really don’t know a great deal about fetching data from a database and my PHP knowledge is basic so I have Frankensteined a few bits together that I have found online!

This is what I have so far, but it doean’t work outside of the foreach which of course means I get multiple arrays (on for each member) rather than just a nice single array of matching counties. I don’t even have a clue if what I am trying to do is possible:

[php]$con = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $con->prepare(‘SELECT memberProperties FROM perch2_members’);
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
$counties = array (
‘Aberdeenshire’, ‘Angus’, ‘Antrim’, ‘Argyll & Bute’, ‘Armagh’, ‘Ayrshire’, ‘Banffshire’, ‘Bedfordshire’, ‘Berkshire’, ‘Berwickshire’, ‘Blaenau Gwent’, ‘Borders’, ‘Bridgend’, ‘Buckinghamshire’, ‘Caerphilly’, ‘Caithness’, ‘Cambridgeshire’, ‘Cardiff’, ‘Carmarthenshire’, ‘Ceredigion’, ‘Cheshire’, ‘Clackmannanshire’, ‘Cleveland’, ‘Conwy’, ‘Cornwall’, ‘County Durham’, ‘Cumbria’, ‘Denbighshire’, ‘Derbyshire’, ‘Devon’, ‘Dorset’, ‘Down’, ‘Dumfries & Galloway’, ‘Dunbartonshire’, ‘East Ayrshire’, ‘East Dunbartonshire’, ‘East Lothian’, ‘East Renfrewshire’, ‘East Riding of Yorkshire’, ‘East Sussex’, ‘Essex’, ‘Fermanagh’, ‘Fife’, ‘Flintshire’, ‘Gloucestershire’, ‘Greater London’, ‘Greater Manchester’, ‘Gwynedd’, ‘Hampshire’, ‘Herefordshire’, ‘Hertfordshire’, ‘Highland’, ‘Inverclyde’, ‘Isle of Anglesey’, ‘Isle of Wight’, ‘Isles of Scilly’, ‘Kent’, ‘Kincardineshire’, ‘Lanarkshire’, ‘Lancashire’, ‘Leicestershire’, ‘Lincolnshire’, ‘Londonderry’, ‘Merseyside’, ‘Merthyr Tydfil’, ‘Midlothian’, ‘Monmouthshire’, ‘Moray’, ‘Neath Port Talbot’, ‘Newport’, ‘Norfolk’, ‘North Ayrshire’, ‘North Lanarkshire’, ‘North Yorkshire’, ‘Northamptonshire’, ‘Northumberland’, ‘Nottinghamshire’, ‘Orkney’, ‘Oxfordshire’, ‘Pembrokeshire’, ‘Perth & Kinross’, ‘Powys’, ‘Renfrewshire’, ‘Rhondda Cynon Taff’, ‘Rutland’, ‘Shetland’, ‘Shropshire’, ‘Somerset’, ‘South Ayrshire’, ‘South Lanarkshire’, ‘South Yorkshire’, ‘Staffordshire’, ‘Stirlingshire’, ‘Suffolk’, ‘Surrey’, ‘Swansea’, ‘Torfaen’, ‘Tyne & Wear’, ‘Tyrone’, ‘Vale of Glamorgan’, ‘Warwickshire’, ‘West Dunbartonshire’, ‘West Lothian’, ‘West Midlands’, ‘West Sussex’, ‘West Yorkshire’, ‘Western Isles’, ‘Wiltshire’, ‘Worcestershire’, ‘Wrexham’
);

foreach($data as $row) {
$myData = json_decode($row[‘memberProperties’], true);
extract($myData);
$result = array_intersect($counties, $myData);
print_r($result);

}[/php]

print_r($result); is obviously just to test what I am getting. Using the array to populate a select box will be the next thing on my list.

Please can someone more knowledgeable help me out here?!

#2

It’s not clear what the array of counties should do. Normally you would just select all counties from the database then use the resulting array from the database to populate the select box.

#3

As I said I am probably going about it the wrong way! The County info is alongside all the other address info within the memberProperties column.

The array of counties is simply because the address form for adding the addresses to the database has a select box for counties - they are not held in the database anywhere as just a list of counties.

I guess I just don’t know how to show the county only once in the select box. For example more than one address would have Essex as the county, but I just need to show it once in the select box. If none of the addresses in the table have Hertfordshire as the county, then I wouldn’t want to show that in the select box.

Am I making sense?!

#4

Hold on, the county in along side the address in a single column?

#5

We all are, that’s why we’re constantly (wanting to) refactoring everything

Sounds like bad db design

Definitely bad db design, here you have data you want to query on which is not possible (in any sensible way) because too much info is crammed into a single column.

This makes sense, but in order to make this properly working county should be its own column in a db table. To make it simple you should consider splitting up the memberProperties column into individual columns so it’s possible to query on it. Then you can simply select all distinct counties so you get a list of unique counties, add an order by to that query and you’re good to go to insert the result into a select box

#6

Hmm, I don’t really have much control over the db design as this is a CMS that I am trying to extend the functionality of (by default you cannot pull any member details to use on the public facing site)

#7

Is there no way about this then? I have just offended the CMS developer by passing on your comments about bad db design so burnt my bridges for any support there I think!

#8

Just because people can code and sell a product, doesn’t mean they know what they are doing. Poking the bear, If he knew what database normalization was, it wouldn’t have been an issue.

How far into this are you? From what I have heard so far, you need a different system, this one will not work long term.

#9

Well my choice of CMS aside, I am working with a search box rather than a drop down now. Can someone please tell me if this is OK?

[code]
Search by County

Search

[/code]

[php]$key=$_GET[‘search’];
if (isset($_GET[‘search’])) {
$con = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $con->prepare(‘SELECT * FROM perch2_members WHERE memberProperties LIKE :search’);
$sth->execute(array(’:search’ => ‘%"’.$key.’"%’));
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach($data as $row) {
$myData = json_decode($row[‘memberProperties’], true);
// $myData should now be a PHP array which you can access easily
extract($myData);
echo “

”;
echo "

$first_name $last_name
";
echo "$city, $county
";
if (isset($facebook)) { echo "Facebook
";}
if (isset($website)) { echo “Website”; }
echo “

”; 
}

if (empty($data)) {
echo ‘No matches found’;
}
}[/php]

#10

[php] $key=$_GET[‘search’];

if (isset($_GET[‘search’])) {
[/php]

These are a bit out of order.

#11

Good point. Do you think it is all OK performance wise?

#12

Other than the connection string being out of place, it would be hard to tell. Doing pre-optimization is more of a hindrance than a benefit. Wait till you have users hitting it under load.

I think that you will be plagued with issues because of the design choices already made, going forward.

#13

Thanks for your advice

#14

[php]extract($myData);[/php]

No, no, no. Do not use extract. Now all of a sudden you have magic variables that appeared out of nowhere.

#15

Oh, what would you suggest instead? As I said I Frankensteined it from bits I found online until I got something that worked!

#16

Actually grabbing the values you are after from the record set is the desirable way.

[php]'SELECT * FROM [/php]
Is also bad practice. You want to specifically request the columns you will use, not just grab anything.

#17

OK, thanks for your help. So now I have this - does this look letter to you?

[php]
if (isset($_GET[‘search’])) {
$key = $_GET[‘search’];
$con = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $con->prepare(‘SELECT memberProperties FROM members WHERE memberProperties LIKE :search’);
$sth->execute(array(’:search’ => ‘%"’.$key.’"%’));
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
if (!empty($data)) {
echo “

    ”;}
    foreach($data as $row) {
    $myData = json_decode($row[‘memberProperties’], true);
    // $myData should now be a PHP array which you can access easily 
echo "<li>";
 echo "<p><strong>".$myData['first_name']." ".$myData['last_name']."</strong> <br />";
echo $myData['city'].", ".$myData['county']."<br />";
if (isset($myData['facebook'])) { echo "<a class='anchor' href='".$myData['facebook']."' target='_blank'>Facebook</a> <br />";}
if (isset($myData['website'])) { echo "<a class='anchor' href='".$myData['website']."' target='_blank'>Website</a>"; }

echo “

”;
}
if (!empty($data)) { echo “”;
if (empty($data)) {
echo ‘No matches found’;
}
}[/php]
#18

This looks out of place and just odd:

[php]if (!empty($data)) { echo “”;
if (empty($data)) {
echo ‘No matches found’;
}
}[/php]

And $myData would only hold the last value in the set.

[php]$sth->execute(array(’:search’ => “%$key%”));[/php]

That does the same thing without worrying about matching quotes.

#19

Sorry, that was just me cutting and pasting that wrong. I was trying to remove all my other bits of code to avoid confusion! Thanks for your help.

Sponsor our Newsletter | Privacy Policy | Terms of Service