Selecting unique information and inserting it at the same time??? 1

[smashing]

I've created a reservation system with PHP scripts hitting a MYSQL database where people reserve to travel on a bus trip. It has been up and running for 2 months now and I'm very happy with it .
I (stupidly) used only one table to collect all the user info. It has columns for their first name, last name, phone number, email address date of travel and so on. The table is filling up and I already have about 9000 rows. But I know for sure that the majority of the customers are returning ones which probably puts the number of unique customers at around 2500. So I created a new table called customers (with an id self-incrementing column) which I'll populate with unique customer info only, and start using an id number for each customer.
My problem now is how to select the people that have the same phone number AND the same first name ('cause I allow separate reservations for husband and wives etc) from the existing table, and put all them into my new table ???
I've tried playing around with SELECT DISTINCT but need help!!

 

[vbkris]

"select count(columnname) from database where name='vbkris'"

this query will return 2 if there are more than one record in the database for that particular name.

now that u have the name u can identify all records for tha name....

Known is handfull, Unknown is worldfull

 

[smashing]

Thanks vbkris.
The thing here is, that I have 9000 names here of which 7000 are duplicates. Do you get the picture now?
I can't go through every name manually and type it in and check if it has a duplicate!!!

 

[sleipnir214]

It seems to me the trick is to remove all the duplicated entries, then dump that data into the new database.

You can detect duplicate entries by performing a LEFT JOIN of a table against itself, looking for identical data columns but different ID columns.

Add an ID column to your existing table. Number your records. Then run a modified form of this PHP script:

<?php
print '<pre>';

$select_query=
&quot;SELECT
	f1. id AS a,
	f2.id AS b
FROM
		foo f1
	LEFT JOIN
		foo f2
	ON
		f1.lastname = f2.lastname AND
		f1.firstname = f2.firstname AND
		f1.id != f2.id
HAVING NOT ISNULL(b)
ORDER BY f1.id, f2.id
LIMIT 1&quot;;

$dbh = mysql_connect ('localhost', 'test', 'test');

mysql_select_db ('test', $dbh);

$result = mysql_query ($select_query, $dbh);

while (mysql_num_rows($result))
{
	$ids = mysql_fetch_assoc ($result);
	
	mysql_free_result($result);
	
	$delete_query = &quot;DELETE FROM foo WHERE ID = &quot; . $ids['b'];
	
	mysql_query ($delete_query, $dbh);

	print &quot;DELETED ID: &quot; . $ids['b'] . &quot;\n&quot;;
	
	$result = mysql_query ($select_query, $dbh);
}
?>

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

Thanks Sleipnir214 or TANSTAAFL! I was actually waiting for you to answer.

Tried your suggestion and am getting a parse error message:

Parse error: parse error in /myfiles/public_html/system/addanother.php on line 168

Line 168 is the one that reads:

    $ids = mysql_fetch_assoc ($result);


Here is the full version of your script (ammended cause I have an existing variable called $result:

$select_query= "SELECT f1.id AS a, f2.id AS b FROM reserve f1 LEFT JOIN reserve f2 ON f1.lname = f2.lname AND f1.fname = f2.fname AND f1.phnum = f2.phnum AND f1.id != f2.id HAVING NOT ISNULL(b)
ORDER BY f1.id, f2.id
LIMIT 1";

$doubles = mysql_query ($select_query, $connection);
while (mysql_num_rows($doubles))
{
    $ids = mysql_fetch_assoc ($doubles);
    
    mysql_free_result($doubles);
    
    $delete_query = "DELETE FROM reserve WHERE ID = " . $ids['b'];
    
    mysql_query ($delete_query, $dbh);
    $inforesult = "DELETED ID: " . $ids['b'] . "\n";
    
    $doubles = mysql_query ($select_query, $connection);
}

 

[skiflyer]

Correct me if I'm wrong, but that's a destructive method you're using smashing, whereas sleipnir's implementation didn't have to be.

I personally think there are easier ways to go about this... but perhaps I'm mistaken. (Or perhaps I didn't read the last two posts thoroughly enough)

Here's what I would do.

Append a row into my mysql table for reservations (the foreign key to the new customer table).

Then, run a query like such...

SELECT id, first_name, last_name, phone_number, and all other contact info
 FROM reserves
ORDER BY phone_number

Then I'd loop through the result set, set a boolean $new_customer everytime I stumbled upon a new first name.
Enter that new customers information into my new customer table.
Read the ID I've assigned them from the new customers table.
Insert that ID as the foreign key into the old reservations table.
Insert that ID as the foreign key into the old reservations table for each record until $new_customer is reset.

Then, I'd go through the reservations and erase ALL the personal information except my new foreign key... that way I keep all the history that's nice to have, and have a nice normalized database.

-Rob

 

[smashing]

Wait a minute skiflyer !
Your'e going a little too fast for a beginer to keep up with!!!

If you can provide an example like Sleipnir214 did that'd be great.

Also, there are more conditions than just 2. The record is unique if the first name last name and phone number (fname, lname and phnum) are the same, otherwise it's considered 2 records.

 

[sleipnir214]

smashing:
My code is intented to delete from the database all duplicate records. I figured that if the data is truly duplicated, then it doesn't need to be there.

I recommend, however, that you copy your original table to a new table and make the deletions there -- I don't know how your existing code will react.

I don't know why it's barfing on mysql_fetch_assoc(), unless you're running a version of PHP earlier than 4.0.3.

Take this section of the code you edited and posted:

while (mysql_num_rows($doubles))
{
    $ids = mysql_fetch_assoc ($doubles);
    
    mysql_free_result($doubles);
    
    $delete_query = "DELETE FROM reserve WHERE ID = " . $ids['b'];
    
    mysql_query ($delete_query, $dbh);
    $inforesult = "DELETED ID: " . $ids['b'] . "\n";
    
    $doubles = mysql_query ($select_query, $connection);
}

And change it to read:

while (mysql_num_rows($doubles))
{
    $ids = mysql_fetch_row ($doubles);
    
    mysql_free_result($doubles);
    
    $delete_query = "DELETE FROM reserve WHERE ID = " . $ids[1];
    
    mysql_query ($delete_query, $dbh);
    $inforesult = "DELETED ID: " . $ids[1] . "\n";
    
    $doubles = mysql_query ($select_query, $connection);
}

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

Afraid I don't have time to put together a detailed example. Sorting on several fields instead of two will be more difficult.

But it sounds like sleipnir and I are on different pages, so let me ask a question explicitly so we're all in the same place.

I understand that you have 1 table which is denormalized and contains both the reservation data which you'd like to keep, and the personal information which you'd like to take out of the table and store elsewhere, correct?

In which case, what you need to do is extract all the unique personal information... then replace it with a foreign key. Making it so that when a repeat customer calls, you can look up their ABC's and have all the other information they've already provided you.

So the trick is finding all the unique personal information, and creating a new table. I'd find my most unique field, and start from there.

$query = SELECT phone FROM reserves

turn that into an array of phone numbers. Then select the other information that's important where phone = phone.

And keep narrowing it down like that.

so imagine...

555-5555: Bob
555-5555: Judy
555-5555: Bob
555-5555: Bob
555-1234: Bill
555-1234: Bill
555-1234: Mary

So, I would do the first query and get

555-5555, 555-1234

Then I'd run something like

SELECT * FROM reserves WHERE phone = "555-5555"
and
SELECT * FROM reserves WHERE phone = "555-1234"

Process them for uniqueness as necessary (in PHP)... creating an array structure like....

foo[555-5555][0][fname]=bob
foo[555-5555][1][fname]=judy
foo[555-1234][0][fname]=bill
foo[555-1234][1][fname]=mary

(The idea is all the [555-5555][0] information would go together)

I'd then turn those back into SQL statements to populate the new table.

After that, the important part is to add a column into your reserve table to hold the foreign key information from your new table. Then erase ALL the personal information from the reserves table.

First things first, make sure you understand the goal... if you don't do a quick google for BNF database or normalized databases and read what pops up.

-Rob

 

[smashing]

To sleipnir: - Be advised I changed it & still getting that same error message (PHP version is 4.2.3 by the way) so what could be the problem???


To Rob: - I'm running out of office now and will check out your suggestion a little later.

Thanks both of you for now.

 

[sleipnir214]

Sorry. A parse error.

The error is probably not on line 168. It's probably somewhere earlier in the script. "Parse error" only means the PHP interpreter got confused, and that it realized it was confused at the time it was looking at line 168.

Start at line 168. Go backward throught the script looking for missing semicolons; unmatched parentheses, braces, brackets, quotes, or doublequotes; other typos.


Keep in mind that the code I gave you exists only to clean up the data you currently have. (I say this because line 168 puts my code somewhere in the middle of yours).

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

Alas sleipnir, I cut out your code and pasted it into a clean page in my text-editor saved it as a PHP script by itself with no other code in it but I still get that same old error message:
Parse error: parse error in /myfiles/public_html/system/test.php on line 15

What to do??????

 

[vbkris]

hey maybe this is what u want:
$sql="select distinct(namefield) from table"
$result=mysql_query($sql);
for($i=0;$i<mysql_num_rows($result);$i++)
{
$row=mysql_fetch_row($result);
$sql=&quot;select count(namefield) from table where namefield=&quot;.$row[0];
//now u don not have to type the induvidual names...
}

Known is handfull, Unknown is worldfull

 

[sleipnir214]

smashing:
Code cut and pasted from Tek-Tips has characters in it that confuses PHP. Specifically, tab characters in code that is cut and pasted into a page will be converted to 5 characters, the hexadecimal ASCII code of which is A0.

Replace those characters with real space characters.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

Thanks both of you for your time, now -

To sleipnir:
Removed all spaces, and I'm now getting a different error message for that same line:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /myfiles/public_html/system/test.php on line 11

And to vbkris:
There are more conditions than just 2. The record is unique if the first name, last name and phone number (fname, lname and phnum) are the same, otherwise it's considered 2 records.
For example there could be 2 distinct people named John Doe with distinct phone numbers, and there can be 2 distinct people nmaed John Doe and Mary Doe with the same phone number.
I think that SELECT DISTINCT will only allow me to select ONE distinct column.


So what now????

 

[sleipnir214]

smashing:
If you're getting that error, then PHP is actually trying to run your code.

That error is most commonly caused by a bad SQL statement's being used in a select query. Check your query to make sure it actually works by pasting it into your favorite MySQL management software.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

Well here goes for starters, When you gave me yesterday:

SELECT f1.id AS a, f2.id AS b

I don't have anything called f1 in my table. But I don't exactly know what to substitute it with (please ignore my ignorance!)

The columns I want to check for uniquness are named fname lname and phnum


Thanks

 

[sleipnir214]

You can specify the name of a table with any column you perform:
select mytable.mycolumn from mytable.

You can also set an alias to a table. These are used when the column names to be selected are ambiguous, for example when you have a query joining two tables and those two tables have column names in common.

Look more deeply into the query. You'll see:

FROM
        foo f1
    LEFT JOIN
        foo f2

f1 and f2 are aliases back to the table names.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[smashing]

With all the copying and pasting done it was inevitable for 3 typos to slip in, so I went through everything, made some corrections and ....

I FINALLY GOT THIS TO WORK!!!

But the loop isn't right because its' only removing one duplicate at a time, each time I'll refresh the screen, it'll remove another one duplicate only.

The error message that comes up now is:
Warning: mysql_num_rows(): 2 is not a valid MySQL result resource in /home/myfiles/system/test.php on line 11



Line 11 is still:

while (mysql_num_rows($result))

 

[sleipnir214]

The code I posted finds one duplicate and removes it, and loops until it finds no duplicates. That's what the while-loop does.

I cannot say what your code is doing.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!