Showing Multiple results based on data

[snyderd]

I am totally Loss as to how to accomplish this..

EXAMPLE OF MY TABLE:

Surname | Polk | Pasco | Orange | Manatee | Hernando

Smith | 10 | 20 | 3 | 14 | 4
Jones | 4 | 25 | 15 | 18 | 6
Snyder | 10 | 2 | 30 | 17 | 1
Sanchez | 1 | 20 | 3 | 12 | 14
Adams | 1 | 28 | 3 | 1 | 40
Klein | 10 | 40 | 3 | 17 | 3

WHAT I NEED TO DO IS: Select from the Table where surname = surname and then show or echo only the county fields greater than 4

RESULTS WOULD BE IN A HTML TABLE (surname is submited from a form):
SO USING: ("SELECT * FROM surnames WHERE surname = '$surname'");
RESULTS OBJECTIVE: Show number of surnames in a county ONLY if greater than 4
RESULTS LIKE BELOW (show field and number/data thats in the table if the number is greater than 4) Like:
Pasco | 25
Orange | 15
Manatee | 18

NOTE: Only shows counties with greater than 4

I am assuming I would need to use some kind of a foreach statement? to break down the results and show only those greater than 4 Maybe ?

I Guess I would need to start with querying the table like:

("SELECT * FROM surnames WHERE surname = '$surname'");

I am really at a loss of where to go from here. Could someone please at least get me started and maybe show an example of code to accomplish this.

Any help is greatly needed and appreciated.

[laserlight]

You have a wrong database design. The relationship between surnames and counties is a many to many relationship, so I would expect a design like this:

Surname table
+----+---------+
| id | surname |
+----+---------+
|  1 | Smith   |
|  2 | Jones   |
|  3 | Snyder  |
|  4 | Sanchez |
|  5 | Adams   |
|  6 | Klein   |
+----+---------+

County table
+----+----------+
| id |   name   |
+----+----------+
|  1 | Polk     |
|  2 | Pasco    |
|  3 | Orange   |
|  4 | Manatee  |
|  5 | Hernando |
+----+----------+

SurnamesPerCounty table
+-----------+------------+-----------+
| county_id | surname_id | frequency |
+-----------+------------+-----------+
|         1 |          1 |        10 |
|         1 |          2 |         4 |
...

This way, selecting all counties for which the surname is listed more than 4 times is a matter of:

SELECT County.name, SurnamesPerCounty.frequency FROM County
    JOIN SurnamesPerCounty ON County.id=SurnamesPerCounty.county_id
    JOIN Surname ON Surname.id=SurnamesPerCounty.surname_id
    WHERE Surname.surname='$surname' AND SurnamesPerCounty.frequency > 4

[snyderd]

laserlight Thank you for your reply and help. I agree I kept thinking something wasn't right when looking at the tables. So.... I have the County Table, and the Surname Table.. If I create the SurnamesPerCounty table as you have specified. Is their away I can move the data (frequencies) from the current surnames table I showed above to the SurnamesPerCounty table as you have suggested ? There are 13,000 records so moving them manually is kinda out of the question. I originally imported the data from an excel spread sheet. Can you help me with a php script to do this or perhaps a sql query i could do from PhpMyAdmin ?

Thank you Very Much for your help..

[laserlight]

Populating the Surname table is easy: you just select the surname field of all the rows of your current table, then insert them into the Surname table.

There presumably is a relatively small number of counties, so you can manually insert them into the County table.

For SurnamesPerCounty:
1. Select the entire Surname table. You construct an associative array that maps surnames to the row ids.
2. Select the entire County table. For each county, select the surname and corresponding frequency from your current table. Loop over the rows retrieved, then insert into the SurnamesPerCounty table. The surname_id is obtained from the array in part 1.

[snyderd]

Laserlight, Thank you for your continued help. I have the surnames and county tables changed and set as:
Surname table
+----+---------+
| id | surname |
+----+---------+
| 1 | Smith |
| 2 | Jones |
| 3 | Snyder |
| 4 | Sanchez |
| 5 | Adams |
| 6 | Klein |
+----+---------+

County table
+----+----------+
| id | name |
+----+----------+
| 1 | Polk |
| 2 | Pasco |
| 3 | Orange |
| 4 | Manatee |
| 5 | Hernando |
+----+----------+

However you lost me on the SurnamesPerCounty I am really not familiar with "construct an associative array that maps surnames to the row ids" and "Loop over the rows retrieved", and I am not really sure how to insert using the results from an array. Would you mind giving me a example of the code.

[WyCnet]

Excuse me,
I'm not trying to butt in or cut anyone off or anything like that, but I noticed that the problem was not well explained, especially the results based on the data, as a result I found it difficult to follow what you were after?

Did you mean in the following excerpt :

Surname | Polk | Pasco | Orange | Manatee | Hernando

Smith | 10 | 20 | 3 | 14 | 4
Jones | 4 | 25 | 15 | 18 | 6
Snyder | 10 | 2 | 30 | 17 | 1
Sanchez | 1 | 20 | 3 | 12 | 14
Adams | 1 | 28 | 3 | 1 | 40
Klein | 10 | 40 | 3 | 17 | 3

that the numbers corresponded to 10 Smiths in Polk, or 3 Adams in Orange, or 25 Jones in Pasco?

If so why were there not results for Jones in Pasco, Orange, Manatee, and Hernando, based on your greater than four crieteria?

It could be that your data is badly organized, but to really verify that you should really clarify what you have, and what you are after, not in implicit terms but explicitally.

[laserlight]

snyderd wrote:

However you lost me on the SurnamesPerCounty I am really not familiar with "construct an associative array that maps surnames to the row ids" and "Loop over the rows retrieved", and I am not really sure how to insert using the results from an array. Would you mind giving me a example of the code.

What have you tried?

[WyCnet]

It could be that you need a compound key, due to the fact that there are over 100 countries and thousands of names. A simple table would have Name, Country and count, which would make Updates, Inserts and Selects quite easy, by both Name and/or Country. Even if the data was relationally seperated, there would still have to be an id, for a name associated with the country and you would have to join the two tables for any meaningful result, whereas a properly indexed table will yield a faster result, by having the key as (Name,Country). However if the Names were needed for purposes other than counting them in a specific country, then relational tables would be the way to gp, as has been suggested.

[laserlight]

WyCnet wrote:

It could be that you need a compound key, due to the fact that there are over 100 countries and thousands of names. A simple table would have Name, Country and count, which would make Updates, Inserts and Selects quite easy, by both Name and/or Country. Even if the data was relationally seperated, there would still have to be an id, for a name associated with the country and you would have to join the two tables for any meaningful result, whereas a properly indexed table will yield a faster result, by having the key as (Name,Country).

My proposal avoids data duplication. The Surname.surname and County.name columns can be indexed if necessary.

By the way, it is "county" not "country" 😉

[WyCnet]

Here is a quick solution, that will be extremely quick.

CREATE TABLE sys1.NameCountry (
	Name varchar(52) NOT NULL,
	Country varchar(24) NOT NULL,
	NameCount int(13) NOT NULL DEFAULT 1,
	PRIMARY KEY (Name,Country))

INSERT into NameCountry 
	(Name,Country,NameCount)
VALUES ('Charest','Canada',DEFAULT)
ON DUPLICATE KEY
	UPDATE NameCount=NameCount+1

SELECT Name, Country
FROM NameCountry 
WHERE NameCount>4

You have the create table, an insert IF NOT update, and the select, but no joins for the relational tables.

Excuse me about the country, instead of County thing.

[snyderd]

Thank you Laserlight and Wycnet for your help, However now I am really confused.. I think I need to explain where I am at in this as my original post was based on using the database tables I had which I think are setup wrong to accomplish what I need to do. At this Point I need to get the Data from the Old into the New.

OLD TABLE THAT I BELIEVE IS NOT CONSTRUCTED CORRECTLY

Surname | Polk | Pasco | Orange | Manatee | Hernando

Smith | 10 | 20 | 3 | 14 | 4
Jones | 4 | 25 | 15 | 18 | 6
Snyder | 10 | 2 | 30 | 17 | 1
Sanchez | 1 | 20 | 3 | 12 | 14
Adams | 1 | 28 | 3 | 1 | 40
Klein | 10 | 40 | 3 | 17 | 3

NEW TABLES:
Surname table
+----+---------+
| id | surname |
+----+---------+
| 1 | Smith |
| 2 | Jones |
| 3 | Snyder |
| 4 | Sanchez |
| 5 | Adams |
| 6 | Klein |
+----+---------+

County table
+----+----------+
| id | name |
+----+----------+
| 1 | Polk |
| 2 | Pasco |
| 3 | Orange |
| 4 | Manatee |
| 5 | Hernando |
+----+----------+

SurnamesPerCounty table
+-----------+------------+-----------+
| county_id | surname_id | frequency |
+-----------+------------+-----------+
| 1 | 1 | 10 |
| 1 | 2 | 4 |
...

I Have ALL Data Entered into the Surname Table and County Table (ass above).

My Challenge Now is to Insert Into the SurnamesPerCounty table The data from the Surname Table, County Table and the Frequencies from my original Table.

So...... I believe what I need to do is somehow..

Select all "surnames" from OLD TABLE

Select all "surnames" from New Surnames Table Where surname = Old Table "surname" and (get ID)

Now This where I Really Get Loss... In the Old Table the Counties are Fields So I guess I need to Somehow..

Select "county" from New County Table Where County Matches the Field Name In OLD Table, Get the ID for the county (from new County Table)

Then get the "frequency" from the OLD Table where the county field = "name" from New County Table, and the "surname" from = "surname"

Then Insert the county_id, surname_id and the frequency into the New SurnamesPerCounty Table.

MAN this seems confusing.. If anyone can give me some code examples of how to accomplish this It would be greatly appreciated.

[laserlight]

laserlight wrote:

1. Select the entire Surname table. You construct an associative array that maps surnames to the row ids.

Roughly, this would be:

$statement = $db->prepare('SELECT id, surname FROM Surname');
$statement->execute();
$surnames = array();
while ($row = $statement->fetch()) {
    $surnames[$row['surname']] = $row['id'];
}

laserlight wrote:

2. Select the entire County table. For each county, select the surname and corresponding frequency from your current table. Loop over the rows retrieved, then insert into the SurnamesPerCounty table. The surname_id is obtained from the array in part 1.

This would be something like:

$statement = $db->prepare('SELECT id, name FROM County');
$statement->execute();
$transfer_statement = $db->prepare('INSERT INTO SurnamesPerCounty VALUES (:county_id, :surname_id, :frequency)');
while ($row = $statement->fetch()) {
    $origin_statement = $db->prepare("SELECT Surname, $row[name] FROM original_table");
    $origin_statement->execute();
    while ($origin_row = $origin_statement->fetch()) {
        $transfer_statement->execute(array(':county_id' => $row['id'],
                                           ':surname_id' => $surnames[$origin_row['Surname']],
                                           ':frequency' => $origin_row[$row[name]]));
    }
}

Of course, I have not tested my code, and this is not the only way to go about doing this transfer of data.

[snyderd]

Laserlight thank you very much for your continued help. I am getting the following error:

Fatal error: Call to a member function prepare() on a non-object in /home/temasnic/public_html/apellidosnicas.net/test/move.php on line 4

Here is the code I am using:

<?php

require('config.php');
$statement = $db->prepare('SELECT id, surname FROM Surnames');
$statement->execute();
$surnames = array();
while ($row = $statement->fetch()) {
$surnames[$row['surname']] = $row['id'];
}
$statement = $db->prepare('SELECT id, name FROM County');
$statement->execute();
$transfer_statement = $db->prepare('INSERT INTO SurnamesPerCounty VALUES (:county_id, :surname_id, :frequency)');
while ($row = $statement->fetch()) {
$origin_statement = $db->prepare("SELECT Surname, $row[name] FROM surnamesold");
$origin_statement->execute();
while ($origin_row = $origin_statement->fetch()) {
$transfer_statement->execute(array(':county_id' => $row['id'],
':surname_id' => $surnames[$origin_row['Surnames']],
':freq' => $origin_row[$row[name]]));
}
}

?>

I have been researching the error and am not finding anything that is working. Any suggestions ?

[Weedpacket]

When posting, please use the markup tags described in the FAQs to mark up your code for readability.

synderd wrote:

Fatal error: Call to a member function prepare() on a non-object in /home/temasnic/public_html/apellidosnicas.net/test/move.php on line 4

That is saying [font=monospace]$db[/font] is not an object; specifically, a database connection. If that is something you're creating in config.php, then the failure lies there (perhaps it's failing, or you're not storing the connection in [font=monospace]$db[/font] at all).

[WyCnet]

The following is an excerpt from the PHP Manual 5.3.1

<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

?> 

You would use:

$db = new PDO($dsn, $user, $password);

[snyderd]

Thank you Weedpacket and Wycnet. I have resolved the connection issue using your suggestion. I now do not get any errors, (i have error reporting turned on, on the server) however it doesnt transfer the data either.
The Code I am Using:

<?php

require('config.php');
$statement = $db->prepare('SELECT id, surname FROM Surnames');
$statement->execute();
$surnames = array();
while ($row = $statement->fetch()) {
    $surnames[$row['surname']] = $row['id'];
} 
$statement = $db->prepare('SELECT id, name FROM County');
$statement->execute();
$transfer_statement = $db->prepare('INSERT INTO SurnamesPerCounty VALUES (:county_id, :surname_id, :frequency)');
while ($row = $statement->fetch()) {
    $origin_statement = $db->prepare("SELECT Surname, $row[name] FROM surnamesold");
    $origin_statement->execute();
    while ($origin_row = $origin_statement->fetch()) {
        $transfer_statement->execute(array(':county_id' => $row['id'],
                                           ':surname_id' => $surnames[$origin_row['Surname']],
                                           ':freq' => $origin_row[$row[name]]));
    }
} 


?>

The Part I am questioning is this Line:
$origin_statement = $db->prepare("SELECT Surname, $row[name] FROM surnamesold");
Specifically $row[name] there is no row "name" in the original table. If I am understanding this correctly, this is where it would select the data from all fields/columns.

Any suggestions any one has is greatly appreciated. I am almost out of Hair to pull out.. Thanks again everyone for your continued help with this.

[laserlight]

snyderd wrote:

however it doesnt transfer the data either

Do some debugging. What PHP statements are actually being run, and for how many times?

snyderd wrote:

The Part I am questioning is this Line:
$origin_statement = $db->prepare("SELECT Surname, $row[name] FROM surnamesold");
Specifically $row[name] there is no row "name" in the original table. If I am understanding this correctly, this is where it would select the data from all fields/columns.

There is no column named "name" in the original table, but there are columns named "Polk", "Pasco", etc. This is why I use $row['name'] rather than name.

By the way, in my example, I wrote:

':frequency' => $origin_row[$row[name]]

This should have been:

':frequency' => $origin_row[$row['name']]

You changed ':frequency' to ':freq', but then you did not make a corresponding change in the statement preparation.

[snyderd]

Laserlight thank you for the reply and continued help. I fixed the line for

':frequency' => $origin_row[$row['name']]  

I also changed the 'freq' to 'frequency' that you pointed out. I am still getting the same results. There is no errors but it doesnt Insert the data. You said

Do some debugging. What PHP statements are actually being run, and for how many times?

I am sorry, but I really dont know what I can do to troubleshoot the problem. I do have error reporting turned on, on the server. It is not producing any errors. If you or anyone has suggestions it would be greatly appreciated.

[laserlight]

snyderd wrote:

I am sorry, but I really dont know what I can do to troubleshoot the problem. I do have error reporting turned on, on the server. It is not producing any errors. If you or anyone has suggestions it would be greatly appreciated.

If you don't know how to debug, now is the best time to start 🙂

One of the first things to do would be to introduce error checking. One way to do this is to enable PDO exceptions for failed operations. Do this before preparing any SQL statements:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

If you then run your script and an exception blows up in your face, then you know that you have a problem associated with the exception. If not, the next step would be to use a debugger to see just what PHP statements are getting (or not getting) executed, and why. If you don't have a debugger and somehow cannot install one, then scatter echo statements before/after various lines (but then you'll have to take care to remove them after debugging).

[WyCnet]

Breaking the code out also helps in understanding what is going on, and this particular example has a double reference so it helps even more.

<?php
require('config.php');
$statement = $db->prepare('SELECT id, surname FROM Surnames');
$statement->execute();
$surnames = array();
while ($row = $statement->fetch()) {
$surnames[$row['surname']] = $row['id'];
}

$statement = $db->prepare('SELECT id, name FROM County');
$statement->execute();

$transfer_statement = $db->prepare('INSERT INTO SurnamesPerCounty VALUES (:county_id, :surname_id, :frequency)');

while ($row = $statement->fetch()) {
$rn = $row['name'] ; $oQuery = "SELECT Surname, $rn FROM surnamesold";
$origin_statement = $db->prepare($oQuery);
$origin_statement->execute();
$countyID = $row['id'];

while ($origin_row = $origin_statement->fetch()) {
    $surnameID = $surnames[$origin_row['Surname']];
    $Frequency = $origin_row[$rn];
    $transfer_statement->execute(array(':county_id' => $countyID ,
                                       ':surname_id' => $surnameID,
                                       ':frequency' => $Frequency ));
}

}

?>