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

smashing · May 11, 2003

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 · May 14, 2003

i think this will work:
select distinct(namefield),lastname,... from table

Known is handfull, Unknown is worldfull

smashing · May 18, 2003

vbkris was right all along!!
Finally got this going like this:

$sql="SELECT DISTINCT fname, lname, phnum, eadd from reserve";
$result = mysql_query($sql, $connection) or die(mysql_error());

$insert_fields = "INSERT INTO customers (id, fname, lname, phnum, eadd)";

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

$display_fields .= " INSERT INTO customers (id, fname, lname, phnum, eadd) VALUES ('', '" . $row[fname]. "', '" . $row[lname]. "', '" . $row[phnum]. "', '" . $row[eadd]. "'); ";

$insert_fields .= " VALUES ('', '" . $row[fname]. "', '" . $row[lname]. "', '" . $row[phnum]. "', '" . $row[eadd]. "')";

$insquery .= mysql_query($insert_fields, $connection);

}
?> <html><head><title>Well Done!</title></head><body>
<p><? echo "$display_fields"; ?></p>
</body></html>

The only problem I'm now having is that whereas $display_fields will loop properly to display the thousands of results on the page, - $insert_fields doesn't loop and only the first row gets into the database. Any idea where I'm going wrong now???

vbkris · May 19, 2003

try this:
for($i=0;$i<mysql_num_rows($result))
{
$row=mysql_fetch_row($result);
$newsql .= " INSERT INTO customers (id, fname, lname, phnum, eadd) VALUES ('', '" . $row[0]. "', '" . $row[1]. "', '" . $row[2]. "', '" . $row[3]. "'); ";

mysql_query($newsql);
}

Known is handfull, Unknown is worldfull

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

smashing

Programmer

vbkris

Programmer

smashing

Programmer

vbkris

Programmer

Similar threads

Part and Inventory Search

Sponsor