New to MySQL and need Help!

I need help on using MySQL

[vreed]

Hello,

This is my fist posting so I hope that people will receive this and respond to my posting.

My question is, I am beginner when it comes to SQL quries. I would like to know if there are any free websites out there that offer good training for SQL Queries?

I also have a database that I would filter out duplicate records. Is there anyone that can help me with this?

Thank you for any help,

Vincent Reed

[edited by: jatar_k at 8:58 pm (utc) on Aug. 27, 2003]
[edit reason] no sigs thx see TOS [webmasterworld.com] [/edit]

[willybfriendly]

I also have a database that I would filter out duplicate records. Is there anyone that can help me with this?

$sql = "SELECT * FROM yourTable WHERE yourField = '$newData'";
$result = mysql_query($sql);
if(mysql_affected_rows($result)>0)
{
//you have a duplicate!
}

Replace "yourField" with individual fields you want to screen.

Haven't finished my coffee yet, but this should be close...yawn

WBF

[vreed]

Is there something else I need to do here. This is what I have.

$sql = "SELECT * TotalUsers WHERE user_ID = '$newData'";
$result = mysql_query($sql);
if(mysql_affected_rows($result)>0)

Also the user_id is the column that I want to filter out the duplicate records.

Thanks,

Vincent Reed

[edited by: jatar_k at 8:58 pm (utc) on Aug. 27, 2003]
[edit reason] no sigs thx [/edit]

[willybfriendly]

Is there something else I need to do here.

Yes, but I can't tell you what it is you need to do since I don't know what you are trying to accomplish. That snippet should let you know that there is a duplicate. You need to write the code that produces the action you want to take in the case of a duplicate.

For instance, if it is part of a sign up routine, you might have

if(mysql_affected_rows($result)>0)
{
echo "That user ID already exists";
}

WBF

[brotherhood of LAN]

There's another way but as willy said, it helps to know more about what you would like to do.

There is an option to have "UNIQUE" values in your mysql table. If you have a database that already exists with duplicates, you could create another database and import the information.

By adding UNIQUE keys you could import the information while dropping the duplicates.

[vreed]

I guess I should be a little clearer. If I don’t ask the question correctly no one can help me J. My apologize. :) I am looking at a table in a Mysql DB. The DB is called sync and the table is called TotalUsers. In this table I have 7 Columns. They are listed below.

1. id
2. user_id
3. first_name
4. last_name
5. email
6. phone
7. tstamp

Might look like this. The pipe is representing a tanew Column.

1 ¦ ab12345 ¦ Vincent ¦ Reed ¦ [e-mail] ¦ 309-765-4393 ¦ 2003-08-26 14:26:52

What i am trying to do is while I am in MySQL Control Center i go to the table and then want to run a query that will either hide or remove the duplicate users from the tables. Is this possible.

$sql = "SELECT * FROM sync WHERE TotalUsers = '$user_id'";
$result = mysql_query($sql);
if(mysql_affected_rows($result)>0)
{
//you have a duplicate!
}

I hope this is more clearer.

Thanks,

Vincent Reed

[edited by: jatar_k at 8:59 pm (utc) on Aug. 27, 2003]
[edit reason] no sigs thx [/edit]

[mikejson]

I'm new to this whole php/mysql stuff but that query doesn't look right.

Your db is sync... you are selecting from that db, it shouldn't appear in the select statement no?

Select <something> from <table> where <condition>

That is a typical select, generalized to any query language really.

Maybe you should look at my post just down from yours, I'm asking somewhat related questions.

[coopster]

You need to replace the italicized/bold parameters with your own information:

<?php 
$db_link = @mysql_connect( 
 'localhost', 
 'userid', 
 'password') 
 or exit('Could not connect: ' . mysql_error()); 
$db = @mysql_select_db('sync', $db_link) 
 or exit('Could not select database: ' . mysql_error()); 
$sql = "SELECT DISTINCT user_id FROM TotalUsers; 
$result = mysql_query($sql); 
while ($row = mysql_fetch_assoc($result)) { 
// returns a list of unique users 
} 
?>

I would also recommend going to the mysql site [mysql.com] and in their manual there is a fairly good tutorial.

[timster]

I see that you're just trying to get rid of the dupes.

It's tricky to delete a row that is an exact duplicate of another row, without deleting both rows. Plus, if you make a mistake, you can hose your data.

You might have better luck inserting your data into a new table designed not to accept duplicates.

(You can make your SQL experience much more pleasant by always, always having a unique numerical ID, typically a primary key, on a table.)

You might have luck doing something like this:

1. Create a new mysql table that looks like sync but required ID to be unique

2. select all rows from original sync, ordered by ID

3. Loop through rows you fetched from sync, inserting their values into the new table
(If a row is a duplicate, the insert will fail.)

Then, if you like, you can back up sync, delete all its rows, alter the table to require ID to be unique, and reimport the table. Otherwise, you can just use the new table.