I'm normalizing some database tables more than I was hoping to get away with. I would have gotten away with it too, if it wasn't for you meddling kids... er, if I didn't need the ability to rename what I was planning to make a key.
Because MySQL doesn't support subqueries (if there was ever a case of worse-is-better, it's PHP and MySQL), the following very simple query:
UPDATE Bookmark_Keywords SET Bookmark_Keyword_Id = (
SELECT Bookmark_Keyword_Id
FROM Bookmark_Keyword_Info
WHERE Bookmark_Keyword_Name = Bookmark_Keyword
)
is going to require that I write procedural code to update the table. Bust.
Update: For future reference, here's the code I had to write:
<?php
$sql = 'SELECT DISTINCT Bookmark_Keyword_Info.Bookmark_Keyword_Id id, Bookmark_Keyword_Name name
FROM Bookmark_Keyword_Info
INNER JOIN Bookmark_Keywords ON(Bookmark_Keyword_Name = Bookmark_Keyword)
ORDER BY Bookmark_Keyword_Name'; //reformatted to fit layout
$rs_id = mysql_query($sql, $conn) or die("Couldn't select your keywords: ".mysql_error());
while($row = mysql_fetch_assoc($rs_id)){
$sql = "UPDATE Bookmark_Keywords
SET Bookmark_Keyword_Id = $row[id]
WHERE Bookmark_Keyword = '$row[name]'"; // reformatted to fit layout
print_r($row);
echo '<br> ';
print_r($sql);
mysql_query($sql, $conn) or die("Couldn't update your table: ".mysql_error());
echo '<br>';
}
?>
Any formatting in there (print_r, echo), etc., was for me to see that the right thing was going to happen before I uncommented the mysql_query line. And of course, this is just "one-off" code... for instance, I didn't addslashes() on $row[name] because I knew none of my names had slashes or quotes in them.
Tuesday, March 9, 2004
Permalink
A discussion of php and worse is better: http://www.sitepoint.com/forums/showthread.php?t=155043