2

(this is my fist post) I have written a PHP-script to enter a "data-set" into a MySQL database. Before I enter the data, I want to check, if the "data-set" is already in the database.

My problem: It only works for a few data-sets (yes, I am sure I didn't forget upper and lowercase) so here is the script (you don't need the german comments... ):

<!DOCTYPE html>
<html><head>
<meta charset="utf-8">
<title>Playlist</title>

<link rel="stylesheet" type="text/css" href="style.css" />

<script type="text/javascript">
setTimeout("self.location.href='index.php'",6000);
</script>

</head>
<body>
<div id="enterbody">
<?php
include ('conf.php');


mysql_select_db("$datenbank");

//Zeit
$date = date("d-m-Y");

//Post 2 var
$inp = $_POST["inp"];
$titel = $_POST["titel"];
$link = $_POST["link"];

//Länge der Strings
$l_inp = strlen($inp);
$l_titel = strlen($titel);
$l_link = strlen($link);

//eingabestrings in kleine zeichen umwandeln
$s_inp = strtolower($inp);
$s_titel = strtolower($titel);

//datenbankstrings in kleine zeichen umwandeln


//Ausgabe

echo "Länge des Interpreten: $l_inp (max: 50)</br>";
echo "Länge des Titels: $l_titel (max: 50)</br>";
echo "Länge des Links: $l_link (max: 42)</br>";

if ($inp == "" or $titel == "")
{
    echo "Bitte fülle die notwendigen Felder aus!";
}
else
{
    if ($l_inp > 50 or $l_titel > 50 or $l_link > 42)
    {
        echo "Der Interpret/Titel/Link ist zu Lange, deshalb wurde er nicht in die Datenbank eingetragen!";
    }
    else
    {       
            //stringkonvertierung nicht vergessen
            $inp_einlesen = mysql_query("SELECT inp FROM $tabelle WHERE inp='$inp'");
                $titel_einlesen = mysql_query("SELECT titel FROM $tabelle WHERE titel='$titel'");

                if (mysql_num_rows($inp_einlesen) == 1 and mysql_num_rows($titel_einlesen) == 1)
                {
                        echo "<b>$inp</b> mit dem Track <b>$titel</b> ist schon in der Datenbank vorhanden, deshalb wird der Datensatz nicht eingetragen";
                }
                else
                {

                $entry = "INSERT INTO playlist (inp, titel, link, date) VALUES('$inp','$titel','$link', NOW())";
                $enter_data = mysql_query($entry);


                if ($enter_data == true)
                {
                    echo "Deine Daten wurden gespeichert! Weiterleitung...";
                }
                else
                {
                                echo "Fehler beim Eintragen der Daten...";
                        }

                }
    }
}



mysql_close($connection);
?>
</div>




</body>
</html>
Improve this question
2
  • Notice: undefined variable: inp, titel, link. XSS & sql injections.... Commented Apr 29, 2012 at 18:34
  • Just a little notice, you could join the two query's content by using an OR statement: SELECT inp FROM $tabelle WHERE inp='$inp' OR titel='$titel' Commented Apr 29, 2012 at 18:43

5 Answers 5

Reset to default
1

On this line:

if (mysql_num_rows($inp_einlesen) == 1 and mysql_num_rows($titel_einlesen) == 1)

You're just testing for one result. Maybe you have multiple results? Maybe two records have the "inp_einlesen" match and only one matches for the 'titel_einlesen'? In other words, if your dataset already has duplicates, this line won't find them unless there is only one duplicate of each field.

Another thing besides upper/lower case is trailing spaces. Be sure to use trim() to remove trailing whitespace.

The other thing you might want to standardize is how you're handling escapes. If someone enters data with apostrophes in it, you'll need to either manually escape this or deal with it at a lower level, like with magic_quotes.

Also, the code here as you've presented it is susceptible to a MySQL injection attack. Be sure to read the PHP man pages for "mysql_real_escape_string."

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. Be sure to select an answer to your question!
1 
 if (mysql_num_rows($inp_einlesen) == 1 and mysql_num_rows($titel_einlesen) == 1)

This means that only and only if one record exists but there must be more than one. Replace ==1 with !=0

And here's an advice to check if variable is empty instead of using $var == '' use empty($var)

Improve this answer

Comments

1

Alternatively, restructure the table and use ON DUPLICATE KEY UPDATE.

Improve this answer

Comments

1

Besides what other people say here, you're probably doing your checks wrong. First of all those your 2 queries can be merged into one:

$dup = mysql_query("SELECT TRUE FROM $tabelle WHERE inp='$inp' AND titel='$titel' LIMIT 1");
if (mysql_num_rows($dup)) {
    // duplicate playlist
}

Now it's easier to see that you're failing only if both fields match. Not sure if you really intended AND there, but even if you did - you'd rather do such check with way, saves a lot on large tables.

Improve this answer

Comments

0

Maybe something like this would work? You can run code if there is already an entry or if there isn't:

if($variable != '') {
    $qry = "SELECT * FROM table WHERE column='$variable'";
    $result = mysql_query($qry);
    if($result) {
        if(mysql_num_rows($result) > 0) {
        //Already exists
        }
        else {
        //Doesn't already exist
        }
    }
    else {
        die("Query failed");
    }

}

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.