Hello, Savior(s)!
I have been given a task to do the following:
"Assume that you have a database with a table named "trainer" with fields (trainerID, userName, fullName, email):
- create this DB and table then insert some random data manually
- create sql query to check if there any duplicate email for different users.
(check all table - hint: use while)
If there are any duplications:
- merge the data by adding the missing data to the old user "ID"
- delete the newest user "ID"
I am required to write the code for a general case scenario where if there exists two rows with the same email, my code should check to see if the rest of the field values are the same or not. Meaning that whatever code I'm going to write can be applied to this table "trainer" and any other table.
My code should be able to see if any users have the same email and remove any duplications found. Where:
If all the field values are the same, do nothing.
If not, then modify the data field values in the oldest row by writing the values from the newest row onto the original data of the oldest row and deleting the newest row.
-------------------------------
Here is my code:
while($q1=mysql_query("select * from trainer"))
{
$email=$q1['email'];
$q2=mysql_query("select * from trainer where email=$email");
$numFields = mysql_query("select * from trainer where email=$email"); //
$count=mysql_num_fields($numFields);
echo $count."<br>"; //to test
if($count>1)
{
$q3=mysql_query("select * from trainer where email=$email");
while($row2=mysql_fetch_array($q3))
{
for($i=0;$i<$count;$i++)
{
if($row[$i]!=$row2[$i])
{
$new=$row2[$i]; //take data
}
else
{
$new=$row[$i]; //keep old
}
}//end for
}//end inner while
}//end if
//updating row
//$updateRow = mysql_query("UPDATE trainer($row)
//SET $new
//WHERE ");
// insert modified data row into 'trainer' table <---------- I have no idea how to update en ENTIRE row altogether without specifying each field
//delete newest row
mysql_query("DELETE $row
FROM trainer
WHERE email=$new['email']")
or die(mysql_error());
}//end outer while------------------------------
Can anyone help me by looking over my code and letting me know what I'm doing wrong?
- Thank you.
P.S.: I am a complete beginner in programming in PHP.