[SOLVED] Needs help Urgent Please!

[studgate]

I have a database setup with five tables

• Music
  
• musicsortbyartist
  
• musicsortbygenre
  
• musicsortbyyear
  
• musicsortbytype
  

I am trying to find a way to edit the data

in the database and update all the tables.

Anybody willing to help, please. People make

mistakes when entering the info, like "batles" instead of "Beatles"

and I want to fix that and update all the tables.

Any help will be greatly appreciated.

Thank you in advance guys, this website is a life saver.

[Barand]

That looks like four tables too many.

[jcbarr]

You are going to have to do some extensive searching if you want to be able to catch all the possible typos that people are going to do...

 

I would combine all those tables in to one as stated before me. And I would create a from that users submit to enter information in to the database and make it show a preview of what they have typed to make sure they can see errors that they have made.

[Jessica]

That looks like four tables too many.

I concur

 

To correct typos like that you could just use a list of common typos.

[studgate]

You are going to have to do some extensive searching if you want to be able to catch all the possible typos that people are going to do...

jcbarr,The Edit function shows me the full form that the user enters and it makes it easy to catch

the mistakes and the only typos I want to fix is the (genre, artist, year, type) anything else can stay the same.

 

That looks like four tables too many.

Barand, the other tables are for the reporting page, people can search by either.

The tables contain, the artist and the number of times he has been added, etc.

Thanks for the suggestions guys, but I really need the help.

 

[Barand]

That looks like four tables too many.

Barand, the other tables are for the reporting page, people can search by either.

 

They are totally superfluous. A single table holding music details, artist, genre, year, type is all that is required. You can search and order that as required.

[studgate]

A single table holding music details, artist, genre, year, type is all that is required

Barand, I do think that you are right but what about the "number", the report sort the data by

number of times the data was added, "artist" "number". If you can show me a way that I can use

one table and be able to get the number of times, i will gladly appreciated it.

[Barand]

$sql = "SELECT COUNT(*) FROM music WHERE artist='Beatles' ";
$res = mysql_query($sql);
$number = mysql_result($res, 0);

echo "There are $number Beatles records";

[Barand]

If you want something like

 

[pre]

Beatles          40

Elton John      35

Spice Girls      20

[/pre]

 

then the query would be

 

$sql = "SELECT artist, COUNT(*) as num
          FROM music
          GROUP BY artist
          ORDER BY num DESC";

[studgate]

If you want something like

 

[pre]

Beatles          40

Elton John      35

Spice Girls      20

[/pre]

 

then the query would be

 

$sql = "SELECT artist, COUNT(*) as num
          FROM music
          GROUP BY artist
          ORDER BY num DESC";

Barand, That's exactly how I want it to look, the problem that I have

is if I only use only one table, how do I insert the "number" into

the database so each time the artist, genre, type or year is enter to increase

each one by one.

I hope you understand what I am trying to say.

[Barand]

You don't. You let the query count the number of entries for each value of artist (or genre etc) for you

[studgate]

I want it to be like that for artist

[pre]

Artist            Number

Beatles          40

Elton John      35

Spice Girls      20

[/pre]

 

I want it to be like that for genre

[pre]

year          Number

Rock              40

Pop                35

Reggae            20

[/pre]

 

I want it to be like that for year

[pre]

Genre            Number

1975                  40

1989                  35

2004                20

[/pre]

 

and the same for type. I hope you understand

 

[jcbarr]

You don't have to insert anything counting the number of times that the artist is there. When you need to display the number of times that they have been added you simply call that query and then echo the results.

[Barand]

For the genre report ir would be

$sql = "SELECT genre, COUNT(*) as num
          FROM music
          GROUP BY genre
          ORDER BY num DESC";

[studgate]

My last question, what will my mysql query look like.

I didn't have the number into the music table, how

should i add it so it can do what I want it do.

CREATE TABLE music
(
  insert example here.
);

 

Thanks!

[Barand]

CREATE TABLE music (
    id int not null auto_increment primary key,
    song_title varchar(100),
    artist varchar(50),
    genre varchar(20),
    year int,
    type varchar(20)
)

 

This can be greatly improved by having separate tables for artist, genre and type, but this is your basic requirement.

[studgate]

This can be greatly improved by having separate tables for artist, genre and type, but this is your basic requirement.

Barand, that's what I was trying to explain.

This was my music table

CREATE TABLE music (
    id int not null auto_increment primary key,
    song_title varchar(100),
    artist varchar(50),
    genre varchar(20),
    year int,
    type varchar(20)
)

plus I had

 

CREATE TABLE `musicbyartist` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `artist` varchar(55),
  `nr` bigint(20) ,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `musicbygenre` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `genre` varchar(55),
  `nr` bigint(20),
  PRIMARY KEY  (`id`)
)

CREATE TABLE `musicbyyear` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `year` int,
  `nr` bigint(20) ,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `musicbytype` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `type` varchar(35) ,
  `nr` bigint(20) ,
  PRIMARY KEY  (`id`)
)

was that wrong??

[studgate]

Anything???

[Jessica]

Are you even reading what they're saying? Let's see.

how do I insert the "number" into the database

You don't. You let the query count the number of entries for each value of artist (or genre etc) for you

You don't have to insert anything counting the number of times that the artist is there. When you need to display the number of times that they have been added you simply call that query and then echo the results.

 

They've already posted code 3 times.

 

Edit: I didn't see page 2  But still.

 

 

[studgate]

The number has been taking car of, now

I am trying to figure out how to display the

php code so it can be display like this:

 

Report by artist
Report by genre
Report by type
Report by year

 

And when someone click on the either, the right

query is executed.

[pre]Artist            Number

Beatles          40

Elton John      35

Spice Girls      20[/pre]

 

Any help??

Thanks in advance.

[Barand]

    * Music

    * musicsortbyartist

    * musicsortbygenre

    * musicsortbyyear

    * musicsortbytype

 

Ah! Sorry, I was thrown by your table-naming which implies that the data is held 5 times in different sequences for reporting purposes. Stick to names of the entity held in the table.

 

    * Music

    * artist

    * genre

    * year  (not  necessary))

    * musictype

   

In which case the music table would be

 

CREATE TABLE music (
    id int not null auto_increment primary key,
    song_title varchar(100),
    artist_id int,
    genre_id int,
    year int,
    type_id int
)

[Barand]

Now your table relationships are

[pre]

artist            music              musictype

=========        =========          ===========

id    -----+    id            +--- id

artist      |    title        |    type

            +---- artist_id    |

                  year          |

          +----- genre_id      |

genre      |      type_id  ----+

========  |

id    -----+

genre

[/pre]

 

As the name of the artist is now held only once, in the artist table, you don't have to worry about it being Beatles in one record and Baetles in another.

 

The query for the artist report, for example, now becomes

 

SELECT a.artist, COUNT(*) as num
FROM music m INNER JOIN artist a
ON m.artist_id = a.id
GROUP BY a.artist
ORDER BY num DESC

 

Putting it all together:

 

For the links to the reports

Reports

<a href='reports.php?name=Artist'>by artist </a><br>
<a href='reports.php?name=Genre'>by genre </a><br>
<a href='reports.php?name=Year'>by year </a><br>
<a href='reports.php?name=Type'>by type </a><br>

 

 

:: reports.php ::

<?php
switch ($_GET['name']) {
    case 'Artist' :
        $sql = "SELECT a.artist, COUNT(*) as num
                FROM music m INNER JOIN artist a
                ON m.artist_id = a.id
                GROUP BY a.artist
                ORDER BY num DESC";
        break;
        
    case 'Genre' :
        $sql = "SELECT g.genre, COUNT(*) as num
                FROM music m INNER JOIN genre g
                ON m.genre_id = g.id
                GROUP BY g.genre
                ORDER BY num DESC";
        break;
        
    case 'Type' :
        $sql = "SELECT t.type, COUNT(*) as num
                FROM music m INNER JOIN musictype t
                ON m.type_id = t.id
                GROUP BY t.type
                ORDER BY num DESC";
        break;
        
    case 'Year' :
        $sql = "SELECT m.year, COUNT(*) as num
                FROM music m 
                GROUP BY m.year
                ORDER BY num DESC";
        break;
}

$res = mysql_query ($sql);
echo '<table border="1" cellpadding="4">';
while (list($descrip, $num) = mysql_fetch_row($res)) {
     echo "<tr><td>$descrip</td><td>$num</td></tr>";
}
echo '</table>';
?>

[studgate]

Barand, when I insert the data into the Music table

what happen to the other tables? Do I have to create the

form to insert data into them too or it automatically does that?

& how can I have the query and the links in one page?

Thank you very much for the help again!

 

[Barand]

Genre and Musictype tables will be relatively static but you'll probably require a form to insert new artists into the artist table. They won't change when you add a new music record.

 

Use the genre,artist and type tables to create selection dropdowns so the user can select from them when entering data to add record into the music table.

 

I didn't say the links and queries are in the same page. However, there is no reason why they can't be. If they are in the same page you can make the links as follows:

 

<a href='?name=Artist'>by artist </a><br>

<a href='?name=Genre'>by genre </a><br>

<a href='?name=Year'>by year </a><br>

<a href='?name=Type'>by type </a><br>

[studgate]

I wanted to know how I go about having the

links & the query in the same page.

Thanks!