[SOLVED] Grab Data From a table, Echo, Then Insert it into a new table...

[Teck]

Alright... I'm really stumped on this one...

 

Basically what I'm trying to do is count the number of times usernames shows up in a table. I then want to reinsert that information into a different table with the Username in column1 & the occurrences in column2 for every user that is echoed on the page..

 

$query = "SELECT username, COUNT(*) AS occurs FROM thread WHERE dateline >= 1189350000 GROUP BY username ORDER BY username";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$username = $row['username'];
$occurs = $row['occurs'];
echo "$username => $occurs<br>";

$query2 = "INSERT INTO data (column1, column2) VALUES ($username, $occurs)";
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
}

 

All that happens is the id for the table increases but leaves the other columns blank...

 

 

Thanks!

[Teck]

bump...

[Jessica]

Are your columns really named column1 and column2? those are bad names.

[JJohnsenDK]

Count your occurs colum instead:

 

$query = "SELECT username, COUNT(occurs) AS occurs FROM thread WHERE dateline >= 1189350000 GROUP BY username ORDER BY username";

[MmmVomit]

Alright... I'm really stumped on this one...

 

Basically what I'm trying to do is count the number of times usernames shows up in a table. I then want to reinsert that information into a different table with the Username in column1 & the occurrences in column2 for every user that is echoed on the page..

 

$query = "SELECT username, COUNT(*) AS occurs FROM thread WHERE dateline >= 1189350000 GROUP BY username ORDER BY username";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$username = $row['username'];
$occurs = $row['occurs'];
echo "$username => $occurs<br>";

$query2 = "INSERT INTO data (column1, column2) VALUES ($username, $occurs)"; // <-----
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
}

 

All that happens is the id for the table increases but leaves the other columns blank...

 

 

Thanks!

 

I think I found the error.  Try changing the line I marked to the following.

 

$query2 = "INSERT INTO data (column1, column2) VALUES ('$username', $occurs)"; 

 

I added two single quotes around $username.

[Teck]

Are your columns really named column1 and column2? those are bad names.

lol no, but when I'm having trouble with something i like to take what ive done and rip it down to be as simplistic as possible as its normally a very simple issue...

 

Count your occurs colum instead:

 

$query = "SELECT username, COUNT(occurs) AS occurs FROM thread WHERE dateline >= 1189350000 GROUP BY username ORDER BY username";

Thank you for the feedback!

 

 

I think I found the error.  Try changing the line I marked to the following.

 

$query2 = "INSERT INTO data (column1, column2) VALUES ('$username', $occurs)"; 

 

I added two single quotes around $username.

That worked! Thank You!