Help with INSERT and ON DUPLICATE KEY

[hugol]

Hi!
Im having problems figure out how to do this....

Im currently inserting values as follow:
Artist Album Rank Peak WeeksOn LastRank Date

Album is a Unique key, so if the Album allready exist, the query updates the row Album with
Rank, Peak, WeeksOn, LastRank and Date.

So far so good...

But, What i really want to do is to insert Album with its values again, if the date is another date than the one currently in the database.
BUT i dont want duplicates if the query is run twice or more!

I imagine that it will look like this:
Artist...Album.....Rank..Peak...WeeksOn...LastRank....Date
Jay Z...Blueprint.10......2........42...........4..............2009-12-28
Alica K..Hola.......4.......1........39...........1..............2009-12-28
Jay Z...Blueprint..4.......2........41...........2..............2009-12-21
Jay Z...Blueprint..2.......2........40...........3..............2009-12-15

Heres my current code:

$genrequery = mysql_query
(" INSERT INTO database
( artist,
album,
rank,
peak,
weekson,
lastrank,
date,
url
)
VALUES
( '".$artist."',
'".$album."',
'".$rank."',
'".$peak."',
'".$weeks."',
'".$lastrank."',
'".$insertdate."',
'".$link."'
)ON DUPLICATE KEY UPDATE date='".$insertdate."',rank='".$rank."',weekson='".$weeks."',peak='".$peak."',lastrank='".$lastrank."'"
);


if($genrequery)
{
echo "<h3>Success</h1>";

}
else
{
echo "<h3>Error</h3>";

}

Thanks for any input at all!
Regards

[hugol]

Ok so I figured this out on my own... I made all the rows as Unique, so it only updates if the data is the same as in row.

Another question:
I get alot of rows this way. Can i minimize the load by spliting the table in to 2?

Table 1
Artist => unique
Album => unique

Table 2
Rank => unique
Peak => unique
Last Rank => unique
Weeks On => unique
Date => unique

How can i link this together?

Imagine that the tables include this data:
Table 1
Artist => Lady Gaga, Album => Monster
Artist => Lady Gaga, Album => Monster Fame
Artist => Elton John, Album => Golden Hits

Table 2
Rank => 1, Peak => 1, Last Rank => 10, Weeks On => 40, Date => 2009-01-01
Rank => 10, Peak => 2, Last Rank => 33, Weeks On => 12, Date => 2009-01-01
Rank => 2, Peak => 1, Last Rank => 10, Weeks On => 2, Date => 2009-01-12

How can i JOIN these tables so It displays relevent data for each artist and album?

[Old Pedant]

Redesign your DB.


Table: Artists
artistid int auto_increment primary key,
artistname varchar(100),
... other artist specific info... age? bio? nationality? whatever

Table: Album
albumid int auto_increment primary key,
albumname varchar(100),
... other album specific info ... year produced? label? number of tracks? ...

-- This table is how you handle the case of multiple artists on a single album!
Table: AlbumArtists
artistid int references Artists(artistid),
albumid int references Albums(albumid)

Table: AlbumRankings
albumid int references Albums(albumid),
rankDate datetime,
Rank int


No reason to store peak or lastRank or WeeksOn in the table.
You can get all those data items from a query on just the above data.

You can add them if you want to, but it could lead to data consistency problems.
For example, if this week's entry says:
rank 13, lastRank 17, peak 13
and then last week's entry says
rank 3, lastRank 22, peak 3
then clearly something is wrong. So why store data that might be wrong??

[hugol]

Thank you for that!
I will try and implement and test it now... How could i get results from this?

Example:
Get current dates top 10 ranks?
Get specific date top 10 ranks?

[Old Pedant]

"Get current date's top 10 albums, by rank."

You have to first define what "current date" means.

Suppose that I have an album that has no records in the table for the last year. But the last entry for it showed that it was number 3 rank. (Okay, unlikely, but bear with me...)

Does that entry qualify it with a 3 rank??

I would assume not.

But where do we draw the line?

Do I look at all records where the rankDate is less than (say) one week old?

I'm assuming that you will *not* rank all albums on the same date. (If you do, the problem is trivial, of course.)

I'm thinking that maybe a more sensible thing to use, in place of rankDate, would be "weekNumber". But of course we could also simply convert rankDate into a week number. Hmmm....

Well, let's start off with assuming that your query *really* meant

Get me the top 10 ranked albums for the week ending today.

so...

SELECT B.albumid, B.albumname, A.artistname, R.rank
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumArtists AS AA
WHERE B.albumid = R.albumid
AND AA.albumid = B.albumid
AND A.artistid = AA.artistid
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.rank
LIMIT 10;

For the same info for any given date, you could just change that one condition on R.rankDate to
AND DATE(R.rankDate) BETWEEN '2009-12-20' AND '2009-12-26'

Substituting in your own actual dates for the two dates in the range.

How well this works depends on how consistent you are about making one entry per week for each ranked album. If you skip weeks here and there, then we have to get more complex.

[hugol]

Thats an exelent example!
I plan on inserting data every week.

Before I say your a genius and a exelent explainer:

I run this querys to insert info to the tables:

$insertdate = $year."-".$month."-".$day; // 2009-12-28
$artistquery = mysql_query
(" INSERT INTO Artists
( artistname
)
VALUES
( '".$artist."'
)"
);


$albumquery = mysql_query
(" INSERT INTO Album
( albumname
)
VALUES
( '".$album."'
)
"
);


$rankingquery = mysql_query
(" INSERT INTO AlbumRankings
( rankDate
Rank
)
VALUES
( '".$insertDate."',
'".$rank."'
)
"
);
echo "Error message = ".mysql_error();

Error message = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Rank ) VALUES ( '2009-12-28', '1*' )' at line 3Error

MYSQL Code used for making the tables:

--
-- Table structure for table `Album`
--

CREATE TABLE IF NOT EXISTS `Album` (
`albumid` int(11) NOT NULL auto_increment,
`albumname` varchar(100) default NULL,
PRIMARY KEY (`albumid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

--
-- Table structure for table `AlbumArtists`
--

CREATE TABLE IF NOT EXISTS `AlbumArtists` (
`artistid` int(11) default NULL,
`albumid` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `AlbumRankings`
--

CREATE TABLE IF NOT EXISTS `AlbumRankings` (
`albumid` int(11) default NULL,
`rankDate` datetime default NULL,
`Rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `Artists`
--

CREATE TABLE IF NOT EXISTS `Artists` (
`artistid` int(11) NOT NULL auto_increment,
`artistname` varchar(100) default NULL,
PRIMARY KEY (`artistid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;


Whats wrong?

[hugol]

BTW:

It inserts these values correct
Artist
Album
Rank

In Table AlbumRankings
album id is NULL
rankDate is NULL

In Table ArtistAlbums
No data

[Old Pedant]

Look more closely at the error message.

'Rank ) VALUES ( '2009-12-28', '1*' )' at line 3

You can't put an asterisk into an INT field.

Also, you really should *NOT* put apostrophes around numeric values, at all.


$rankingquery = mysql_query
(" INSERT INTO AlbumRankings
( rankDate
Rank
)
VALUES
( '" . $insertDate . "'," . $rank . ")"
);

Notice no apostrophes around $rank.

[Old Pedant]

In Table ArtistAlbums
No data

Ummm...and just where in your code do you *DO* any INSERT into that table???

Not in any code you showed, at least.

Plus, you aren't inserting an albumid in the rankings table, either.

[hugol]

The problem is that the rank could contain a * after the number...
$rank = mysql_escape_string($v['rank']);

It still inserts rank 1* as 1. hm?

How will i get the right albumid in rank?

No, i dont insert any data to ArtistAlbums
How would that code look like?

I lack knowledge.:confused:

[hugol]

Even if I do

$getrank = mysql_escape_string($v['rank']);
$rank = preg_replace("/[^a-zA-Z0-9\s]/", "",$getrank);

it echos the same error

Error message = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Rank ) VALUES ( '2010-01-02', 1 )' at line 3Error

[Old Pedant]

Hmmm....maybe RANK is a keyword in MySQL???

Probably, come to think of it.

Okay, so either change the field name or just put backticks around the name:

`Rank`

The backtick shares a key with the tilde ~ character.

***********

INSERT INTO ArtistsAlbums( artistid, albumid ) VALUES( 7, 31 );

Or use whatever the appropriate values are for the given artistid and albumid. If you don't *know* the right id, you could query for it. If you want it to be the same value as that autogenerated value from a prior INSERT, then *after* the insert you can do

SELECT LAST_INSERT_ID();

immediately after the insert to get the new number.

[hugol]

Thanks!
I looked at the code more carefully, theres a *,* missing after rankDate in the query...:rolleyes:

It is working now! Thank you...
How ever, ;)
I need to figure out what code yo use in the ArtistAlbum table... I will post back later.
Thank you very much!

[hugol]

Im having some problems with the query to echo the results...

Im not using the ArtistAlbums table, but I implemented another table called AlbumType, wich contains tyename and typeid.

I want to insert Artist, Album, Type, Rank and Date
Example:
Lady Gaga Fame Dance/Electronic 2 2009-12-28
Lady Gaga Fame Europe 1 2009-12-28
Lady Gaga Fame UK 3 2009-12-28

This I have achieved.
But

$search = mysql_query
(" SELECT B.albumid, B.albumname, A.artistname, R.rank T.typename
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumType AS T
WHERE B.albumid = R.albumid
AND A.artistid = A.artistid
AND T.typename = T.typeid
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.rank
LIMIT 10
");

echo mysql_result($search);


is not giving me any results, infact its giving me error.
Im very new on mysql join and a as b.... :(
Please help?

[Fumigator]

You define your query...


$query = "SELECT * FROM mytable";


You generate the query resultset (and check for errors)...


$result = mysql_query($query);
if (!$result) {
echo "Query Error. Query text is $query<br />Error is ".mysql_error());
}


You then fetch rows out of the resultset:


$row = mysql_fetch_assoc($result);


If there are many rows, do a loop:


for ($i = 0; $i < mysql_result_rows($result); $i++) {
$row[$i] = mysql_fetch_assoc($result);
}


Now you have all your data and can do with it what you want. Echo it, or whatever.

[hugol]

Does this work if i have 4 tables as:
Albums
Artists
AlbumType
AlbumRankings
?
Example:
Albums:
Albumid: 1 Albumname: The Fame Typeid: 1

Artists:
Artistid: 1 Artistname: Lady Gaga

AlbumType:
Typeid: 10 Typename: Dance

Albumrankings:
Albumid: 1 RankDate: 2009-12-28 Rank: 1

[Fumigator]

I don't see why not.

[Old Pedant]

No, because you haven't linked the artist to the album in any way.

As I said, *IF* each album has ONLY ONE artist, then could put artistID into the Albums table.

But in the normal case where one album *can* have many artists, you need the AlbumArtists table that I showed you. So 5 tables.

[hugol]

ok!
So I inserted albumid and artistid so it could be linked.

running this wont work:

$search = "SELECT B.albumid, B.albumname, A.artistname, R.rank
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumArtists AS AA
WHERE B.albumid = R.albumid
AND AA.albumid = B.albumid
AND A.artistid = AA.artistid
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.rank
LIMIT 10";

$result = mysql_query($search);
if (!$result) {
echo "Query Error. Query text is $query<br />Error is ".mysql_error());
}

$row = mysql_fetch_assoc($result);

for ($i = 0; $i < mysql_result_rows($result); $i++) {
$row[$i] = mysql_fetch_assoc($result);
echo $row[$i];
}

Im guessing that theres something wrong ;)
Thanks for both of your's interest in my problem!

[Old Pedant]

And what does "not work" mean???

And did you try the query with some DB tool, not just in your own PHP code?

[hugol]

Parse error: syntax error, unexpected ')', expecting ',' or ';' on line 30
Dont know extacly how to put the ' or ) right...
No i didnt test it with any db tool.

[Old Pedant]

That looks like a PHP error, to me.

Your SQL code isn't 30 lines long (in fact, to MySQL it is only 1 line long). So line 30 means line 30 of the PHP.

I'm not a PHP person.

[hugol]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY R.rank LIMIT 10' at line 6

[Fumigator]

The code I provided isn't bug free, it's an example of the logic you should use.

I meant to say:


if (!$result) {
die("Query Error. Query text is $query<br />Error is ".mysql_error());
}


And you should be using mysql_num_rows(), not mysql_result_rows()-- I misstyped.

[hugol]

I think i tryed many version of the code and i cant get result from it.
So i wrote this to see if i would get any result at all:


$ranking = mysql_query("SELECT * FROM AlbumRankings WHERE rank = '2' AND typeid = '1'");

while($row = mysql_fetch_array($ranking))
{
$rankalbumid = $row['albumid'];
$rank = $row['Rank'];
}

$album = mysql_query("SELECT * FROM Album WHERE albumid = '".$rankalbumid."'");

while($row = mysql_fetch_array($album))
{
$albumid = $row['albumid'];
$albumname = $row['albumname'];
$albumtype = $row['typeid'];
}


$albumtypes = mysql_query("SELECT * FROM AlbumType WHERE typeid = '".$albumtype."'");

while($row = mysql_fetch_array($albumtypes))
{
$typename = $row['typename'];
}

$albumartist = mysql_query("SELECT * FROM AlbumArtists WHERE albumid = '".$albumid."'");

while($row = mysql_fetch_array($albumartist))
{
$artistid = $row['artistid'];
}

$artistname = mysql_query("SELECT * FROM Artists WHERE artistid = '".$artistid."'");
while($row = mysql_fetch_array($artistname))
{
$artistnames = $row['artistname'];
}

echo "Album Number '".$albumid."' is '".$albumname."' by '".$artistnames."' whith Rank '".$rank."' and the type is '".$typename."'";

Which echos:
Album Number '3' is 'Reality Killed The Video Sta...' by 'Robbie Williams' whith Rank '2' and the type is 'European Albums'

which is right... but, the code isnt that nice is it?
could you/any one, give me a better example that echos all the results from a given albumtype and rank?
Like top 10 european albums last week

thank you

[Old Pedant]

Look, the query you used in post #19 *IS* the right one.

TRY IT IN A DB TOOL, instead of in PHP code, and see if you get a more meaningful error message.

I also already told you that *possibly* RANK is a MySQL keyword. So you could put the backtick marks around it if that's the problem. Thus:

SELECT B.albumid, B.albumname, A.artistname, R.`rank`
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumArtists AS AA
WHERE B.albumid = R.albumid
AND AA.albumid = B.albumid
AND A.artistid = AA.artistid
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.`rank`
LIMIT 10;

Get that VERY BASIC query working first and *then* we can expand it to include album type, etc.

But, again, not with PHP. Not until it works in the DB tool.

[Old Pedant]

What the heck, let's add in the album type, too:

SELECT B.albumid, B.albumname, A.artistname, T.typename, R.`rank`
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumArtists AS AA, AlbumType AS T
WHERE B.albumid = R.albumid
AND AA.albumid = B.albumid
AND A.artistid = AA.artistid
AND T.typeid = B.typeid
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.`rank`
LIMIT 10;

And you could limit it to a single type easily:

SELECT B.albumid, B.albumname, A.artistname, T.typename, R.`rank`
FROM AlbumRankings AS R, Albums AS B, Artists AS A, AlbumArtists AS AA, AlbumType AS T
WHERE B.albumid = R.albumid
AND AA.albumid = B.albumid
AND A.artistid = AA.artistid
AND T.typeid = B.typeid
AND T.typeid = 1
AND R.rankDate > DATESUB( CURDATE(), INTERVAL 1 WEEK )
ORDER BY R.`rank`
LIMIT 10;

[hugol]

Thanks alot!

I runned your code in MyDB Studio and got the same error.
Changed it to

AND R.rankDate > DATE_SUB(CURDATE(),INTERVAL 1 WEEK)


And it works!:thumbsup:

[Old Pedant]

Ahhh...yep, a silly typo on my part. But that's why testing it in an external program is so much better: You isolate the SQL coding errors from the server (PHP) coding errors.

Can't believe I stared at that chunk of SQL so many times and still missed that. Sorry!