Making progress! A followup conundrum

badgoat

First off, thank you to all who have helped me with my initial problem last week. I feel that I am at the cusp of understanding the lines of code, rather than copying and pasting and praying 🙂

Today's problem is this... My little script, which I use to add a song name and an artist, works! When I add a new song and artist and click submit, they display correctly on the next page and the data saves correctly to the database. The problem lies in when I use the drop down box to select an artist previously added in, the artist is not displayed on the 2nd page, and the artist data is not saved to the database. The song is there, but the artist is not.

Questions:

What is wrong with my scripts which prevents the drop-down menu from correctly adding in the artist?
Why does the selection of an artist in the drop down menu automatically send me to the next page, rather than waiting for me to click the SUBMIT button?
What do I do to prevent duplicate entries?

Below are my two scripts, as edited with the help of the board (Thanks again!)

makemusic.php (This is where I enterthe song/artist)

<html>
<head>
<title>Add</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">


<?php

$connect= mysql_connect("localhost","root","cpyter") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("music") 
   or die("Could not select music database !");

 ?>

<table width="350" border="0" align="center" cellpadding="0" cellspacing="0" height="277">
  <tr> 
    <td bgcolor="#000000" height="2"> 
      <div align="center"><b><font color="#FFFFFF">ADD A RECORD</font></b></div>
    </td>
  </tr>
  <tr> 
    <td width="48%" height="121" bgcolor="#CCCCCC"> 
      <form name="form1" method="post" action="makemusic1.php">
        <table width="75%" border="0" align="center">
          <tr> 
            <td width="36%"><b> Name</b></td>
            <td width="64%"> 
              <div align="center"> 
                <input type="text" name="songname" size="20">
              </div>
            </td>
          </tr>				
					<tr>          

            <td colspan=2> 
              <div align="center"> 


<select name="artist" tabindex="4" onChange=submit()>
   <option value="notset">- or select an artist -</option>
<?
//--- CREATE ARTIST SELECT ---
$sql = "SELECT DISTINCT groupname FROM songs ORDER BY groupname";
$groupname = mysql_query($sql) or die($sql . '<br />' . mysql_error());
while ($row = mysql_fetch_array($groupname)) {
   echo '<option value="' . $row['groupname'] . '">' . $row['groupname'] . '</option>';
}
?>
</select> 


          </div>
        </td>
      </tr>
      <tr> 
        <td width="36%"><b>Group</b></td>
        <td width="64%"> 
          <div align="center"> 
            <input type="text" name="groupname" size="20">
          </div>
        </td>
      </tr>

      <tr> 
        <td colspan="2"> 
          <div align="center"> 
            <input type="submit" name="Submit22" value="Submit" action="required">
          </div>
        </td>
      </tr>
    </table>
  </form>
</td>
  </tr>
</table>
</body>
</html>

makemusic1.php (This is the page that displays the newly-entered song/artist)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Untitled</title>
</head>
<body>
<?php

$connect= mysql_connect("localhost","root","cpyter") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("music") 
   or die("Could not select music database !");
$sqlquery = "INSERT INTO songs VALUES('','". $songname ."','". $groupname ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");
echo "<table border=1 align=center  width=500>";
echo "  <tr> ";
echo "    <td> ";
echo "     Song Name";
echo "    </td>";
echo "    <td>".$songname. "</td>";
echo "  </tr>";
echo "  <tr> ";
echo "    <td > ";
echo "      Group";
echo "    </td>";
echo "    <td >".$groupname. "</td>";
echo "  </tr>";
//echo "  <tr> ";
//echo "    <td > ";
//echo "      Song ID";
//echo "    </td>";
//echo "    <td>".$songid. "</td>";
//echo "  </tr>";
//echo "  <tr> ";
//echo "    <td colspan=2> ";
//echo "     Add another record <a href="http://localhost/info/makemusic.php">LINK</a>";
//echo "    </td>";
//echo "  </tr>";
echo "</table>";
?>
Add another record <a href="http://localhost/info/makemusic.php">LINK</a>
</body>
</html>

alfoxy

Answer to Q2 first

Its because of the line

the javascript tells the browser to submit when the drop down has changed in any way - onChange=submit() - get rid of this bit

alfoxy

Now the rest

Your artist dropdown is called "artist" but your entry into the database says "groupname" i.e.

$sqlquery = "INSERT INTO songs VALUES('','". $songname ."','". $groupname ."')";

what you need is to check if someone entered a group name or selected an artist

if($groupname)
{enter_into_db = $groupname;}
else
{enter_into_db = $artist;}

$sqlquery = "INSERT INTO songs VALUES('','". $songname ."','". $enter_into_db ."')";

this should work

alfoxy

Oh and Q3

$result_unique=mysql_query("
SELECT * FROM songs
WHERE song= '$songname'
AND ( artist='$artist' OR artist='$groupname')
");

Im assuming you called your table fields song and artist, if not insert your own names.

then

if(mysql_numrows($result_unique) > 0)
{echo "Song already exists<br>";}
else
{
// insert the new song
}

badgoat

Thank you very much Al!

Here's my MySQL table, explained:

Query:
explain songs;

Result Set:

Field Type Null Key Default Extra
songid bigint(20) PRI auto_increment
songname varchar(20)

groupname varchar(20)

I put in the fix you suggested for Question #2, and that fixed it.. (Thanks!) But when I inserted to fix problem #1, I get a parse error. I haven't tackled the answer to Q 3 yet, still readin git and trying to figure out where to insert it. :queasy:

EDIT

After adding in the fix for Q3, I get this error:
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-7\www\info\makemusic2.php on line 27

alfoxy

For Q1 just remove onChange=submit()
so you should be left with

that shouldnt give an error !?

As for Q3 your query should read

$result_unique=mysql_query("
SELECT * FROM songs
WHERE songname= '".$songname."'
AND ( groupname='".$artist."' OR groupname='".$groupname."')
");

Note the single quote, double quote, dot before the variables
and dot, double quote, single quote after them
i.e -> ' " . $songname . " '

Try that

badgoat

My pardons Al, you are correct, that did fix Q1. I misread the order of my own questions.

How doe sthat affect this snippet?

if($groupname)
{$enter_into_db = $groupname;}
else
{$enter_into_db = $artist;}

I have no variable named $artist

EDIT:

Also, I am getting a warning error
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-7\www\info\makemusic2.php on line 27

Getting this error whether I use the mysql_num_rows or mysql_numrows.. I tried mysql_num_rows instead after looking at the php.net website.

alfoxy

Your dropdown is called artist, removing the onChange=submit() bit just means it wont submit as soon as you choose an item from the dropdown.

that first checks if the user typed in a group name
if yes then use the group name variable (textfield)
otherwise
use the artist variable (dropdown)

badgoat

aaah, I gotcha 🙂 Yes, the dropdown menu is artist. Pardon me

Here's the output when I enter a new song and an existing artist:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-7\www\info\makemusic2.php on line 27
Song Name Godzilla
Group

Add another record LINK

Roger_Ramjet

As my sig warns, Badgoat, you copy and paste my code at your peril. I generally just copy a suitable example from my own project that I'm working on. Sometimes I run a replace or 2 on field names etc. Usually this means that it ends up being booby-trapped, which forces you to understand what you are doing. It's no good just using code you don't understand - it could be doing anything. I hope you have enjoyed learning all that you have this last week and good luck with your project.

badgoat

Yep.. The learning curve is a little steep for me, being from a non-programmer background. I programmed in Basic on the Commodore 64 back in the 80's, but nothing since then. I am enjoying the learning process, but it is easy to get lost or overwhelmed. So instead of taking my project at work as a whole, I compartmentalize it and try to do one little thing at a time. It makes it easier for me this way.

alfoxy

That means there is somthing wrong with

$result_unique=mysql_query("
SELECT * FROM songs
WHERE songname= '".$songname."'
AND ( groupname='".$artist."' OR groupname='".$groupname."')
");

what its doing is selecting all the songs from the db whose song is = $songname and groupname is = $artist or $groupname. If there is 1 or more then its already in the database.

Before you run the mysql_numrows query echo the query first to see what exactly is being run

ie change it to a string variable first

$sql = "
SELECT * FROM songs
WHERE songname= '".$songname."'
AND ( groupname='".$artist."' OR groupname='".$groupname."')
";

$result_unique=mysql_query($sql);

if you enter the song as Godzilla and the groupname as group101 then echoing $sql should give you ...

SELECT * FROM songs
WHERE songname = 'Godzilla'
AND (groupname='' OR groupname='group101')

test that and let me know what happens

Roger_Ramjet

Q3. the answer with a database is ALWAYS put the constraints on the table if you need to enforce eg unique entries. This is so that the data does not become corrupted if someone forgets to enforce it in code one time. So you create a unique index on the songs using the two columns: groupname and songname.

mysql column indexes

You can use the query return to see if the song was already in the table.

badgoat

Thank you Al, yes, that took care of the Warning error.

Now, when I add a song and artist, and go back to the beginning to add another song by the same artist, there is no data in the $groupname output on the 2nd page, although the correct groupname is indeed saved to the MySQL database..

ie, I cannot tell which group I just entered without checking the MySQL database.

s my code incorrect from the output page?

echo "<table border=1 align=center width=500>";
echo " <tr> ";
echo " <td> ";
echo " Song Name";
echo " </td>";
echo " <td>".$songname. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td > ";
echo " Group";
echo " </td>";
echo " <td >".$groupname. "</td>";
echo " </tr>";
echo "</table>";

EDIT:

I fixed it. OMG, some of this is actually leeching in :p

I changed line

echo " <td >".$groupname. "</td>";

echo " <td >".$artist. "</td>";

and now it displays correctly.

alfoxy

That will only work now for artists (from the drop down) but not groups (text field)

What you want to do is print out the value that was actually entered into the db .... ie $enter_into_db ( very poor naming by me by the way! )

so change

echo " <td >".$groupname. "</td>";
to
echo " <td >".$enter_into_db. "</td>";

and thats it

badgoat

🙂 Yep, looking back thru the code, that would seem to be the correct way to do it 🙂

Thank you very much for all the help!

/til next week, when I have a new problem...

badgoat

I thought of another related question...

Once I select an existing artist in thr drop down menu, is there a way to make it propogate the information into the text field provided?

Now let's say that when I enter an existing artist from the drop down menu, I want his label, birthday, and last CD to populate as well. I am sure that's possible, and probably with a few lines which would tell to echo the row from the MySQL database... Is that right?

alfoxy

yep that would be fine

just have a seperate table, called say artistinfo

do a search to see if any artists of that name exist (as in the other example) if they exist print out his/her other details from artistinfo