very basic php and sql test

[phpforfun]

so I got a test sent to me, if I pass the SQL/PHP test, I get it... I think, not sure, all I know is a guy sent me an email with a TON of information asking if I knew it all, I said no (AJAX, OOP, JS, stuff like that), and he said well maybe I can still work with you, then sent me this test.. I just filled it out. Should I add anything to spice it up? its pretty basic. Ill turn it in in a few hours.

<?php

//Database Connection
$msdb = mysql_connect("localhost", "root", "");
mysql_select_db("test", $msdb) or die(mysql_error());

/*
GENERAL INFORMATION:
    Below is the current table structure for 'members':
=====
ID: id (autoincrement)
firstname
lastname
phonenumber
=====


PUT YOUR NAME HERE PLEASE: 


PART 1:
-------
    * Below, write the PHP code to insert a first name, last name, and phone number
into the 'members' table.  Use an HTML form to collect this information, then store it to the 'members' table.

// 

*/
$form = '<form id="dataForm" name="dataForm" method="post" action="">
      First Name: 
      <input name="firstname" type="text" id="firstname" />
      <br />
      Last Name: 
      <input name="lastname" type="text" id="lastname" />
      <br />
      Phone Number: 
      <input name="phone" type="text" id="phonenumber" />
      <br />
      <input type="submit" name="Submit" value="Submit" />
    </form>';

if(isset($_POST['firstname'], $_POST['lastname'], $_POST['phone'])){
    $firstname = htmlspecialchars($_POST['firstname']);
    $lastname = htmlspecialchars($_POST['lastname']);
    $phone = htmlspecialchars($_POST['phone']);

    $sql = mysql_query("INSERT INTO `members` (`firstname`, `lastname`, `phone`) VALUES ('$firstname', '$lastname', '$phone')");

    mysql_query($sql) or die (mysql_error());
} else {
    echo $form;
}



/*
PART 2:
-------
    * Below, write the PHP code to retrieve the previously inserted record from the
'tests' table and display it in the browser.
//*/

if(empty($_POST['fetchID'])){
    $fetchID = $_POST['fetchID'];
    $sql = "SELECT * FROM `members` WHERE `id`='$id'";
    $get = mysql_query($sql);
    $id = $get['id'];
    if($fetchID != $id){
        die("That id does not exist!");
    }
    $firstname = $get['firstname'];
    $lastname = $get['lastname'];
    $phone = $get['phone'];
    echo "First Name: $firstname <br>Last Name: $lastname <br> Phone Number: $phone";
} else {
    echo '<form id="dataForm" name="dataForm" method="post" action="">
      Select an ID: 
      <select name="fetchID" id="selectID">';
    $sql = "SELECT * FROM `members` ORDER BY id";
    $result = mysql_query($sql, $msdb);
    $rows = mysql_num_rows($result);
    for ($i = 0; $i < $rows; $i++) {
        $id = mysql_result($result, $i, 'id');
        echo '<option value="'.$id.'">'.1.'</option>';
    }
    echo '</select>
      <input type="submit" value="Submit" />
    </form>';
}


?>

pretty basic I know.. just anything to spice it up?

[Jordan]

A few points....

1) You could use a prepared MySQL statement. This adds further security against the tainted values for your insert statement.

2) I see no comments. He may be looking for well written comments to see if he can work with you or not.

3) Use the ctype functions to check the user data. When you are expecting a username but you get an number you want to throw an error. You want fetchID to be a number in part 2.

4) Am I missing something here? Why is $id defined after it is used?

   $fetchID = $_POST['fetchID'];
    $sql = "SELECT * FROM `members` WHERE `id`='$id'";
    $get = mysql_query($sql);
    $id = $get['id']; 

Did you test this?


There may be more wrong but I'm heading to bed.

[phpforfun]

havent tested it, and what I do to check if there is in fact the data in the database that they selected, I try to fetch it, if it doesnt exist, then it will throw the error..

$fetchID = $_POST['fetchID'];
//gets POST id data
    $sql = "SELECT * FROM `members` WHERE `id`='$fetchID";
//gets the id from the database, but if they input a fake id, then it wont exist..
    $get = mysql_query($sql);
    $id = $get['id']; 

*fixed*
thats how I learned to check if the data exists in a mysql table, I learned it from a tutorial that jaan had posted.

1) You could use a prepared MySQL statement. This adds further security against the tainted values for your insert statement.

I thought that was a prepared mysql statepent... perhaps im wrong.

[phpforfun]

fixed the error, added some comments, attached it, explained why im so darn special, and now I hope I get the job

[Jordan]

No, you have to use MySQLi for prepared statements. You can see what they look like in one of my blogs: PHP MySQL Improved

[jessje]

Thanks for the very good info, you're a real life saver

[John]

You can also use PDO: PHP: PDO - Manual

[phpforfun]

I got the job

[Jordan]

Congrats!

[phpforfun]

yeah, I like it, here are some details.

they are located in utah, thus I work at home if I take a contract, (this is a second job, not a primary), the only thing they require is a VOIP client and a headset to chat with people.

first job I get $100 bonus for doing it, I get an extra 10% if its done on time, and an extra 10% if I make the customer happy.

the next 4 jobs I still can get the 2 10% bonus deals, just not the 100$ bonus, if I get those done and im good enough, they will let me take more than 1 job at a time, they said they "NEVER" run out of jobs. the base I get is 20% of each deal, deals go from $100 to $5000. and a few deals later, if its all good, I can get bumped up to 40%.

not bad