[mattrmclaren]

An application I'm working on requires that I store certain values in a database (coming from a submitted form). Like for example:

$sql="INSERT INTO Employment (JobTitle, StartDate, EndDate) VALUES('$_POST[jobtitle]','$_POST[startdate]','$_POST[enddate]')";

Once they're in the database, how do I access them individually in the following context:

 if ($jobtitle=='123') {
        echo "You're a software developer.";
    } elseif ($jobtitle=='1234') {
        echo "You're a software tester.";
    }

Essentially, it's a recommender system where I need to be able to issue recommendations based off of the data in the database. I.e., if job title is this, if start date - end date = this, etc. I'm a beginner with mySQL. Any help would be greatly appreciated.

[e_i_pi]

To access the variables, you need to first retrieve them from the database. Think of the database as a kind of warehouse. When you INSERT something, it's storing it in the warehouse. In order to retrieve something, you need to SELECT it.

To build your SELECT statement, you'll need to know what you're selecting. For instance, you might be selecting all records where the employment is between two dates, or you might want to select records for a particular employee.

To be able to help more, I'll need to ask a couple of questions:

• That insert statement doesn't seem to reference a particular employee. Is there a reason why?
  
• What does the "Employment" table look like? In other words, how many columns does it have, and what sort of information is stored in those columns?
  
• What actual information do you want to retrieve? Is it information on one particular employee, or multiple employees?

[mattrmclaren]

So it's done on a user by user basis. In the context of this example, the database table has job title, start date and end date fields/columns. I'm just using a general database for this hypothetical user. The information I want to retrieve is just on the active user, but I don't want to display it on the front end, I need to use it on the back end to issue recommendations. So if the user has a certain job title or majored in a certain subject in college, etc. certain recommendations would be issued based on that data.

[e_i_pi]

In the Employment table, you don't seem to be recording the user ID at all. Is there really only three columns to that table? How will you determine which row of the Employment table pertains to a certain employee?

[mattrmclaren]

I was just starting out simple to figure out how to make recommendations based on data stored in a database before moving on from that. The first row would be a user ID. I also know how to construct a SELECT statement but my question is, how do I convert the results of the SELECT statement into variables I can access within the user class, as denoted above? I.e. if job title is this, recommend that, if college major is this, recommend that, etc.

This post has been edited by mattrmclaren: 11 December 2011 - 07:55 AM

[e_i_pi]

How are you executing the INSRET statement? There's a few ways you can do it. I would recommend using Prepared Statements and PDOs, though a lot of people opt for the mysql_* or mysqli_* functions.

[mattrmclaren]

$con = mysql_connect("localhost","root","stewie11");
        if (!$con)
            {
        die('Could not connect: ' . mysql_error());
            }
            mysql_select_db("user", $con);
            $sql="INSERT INTO Employment (JobTitle, StartDate, EndDate) VALUES('$_POST[jobtitle]','$_POST[startdate]','$_POST[enddate]')";

        if (!mysql_query($sql,$con))
            {
        die('Error: ' . mysql_error());
            }
            echo "1 record added";

        mysql_close($con)

[e_i_pi]

While I would advise against using the mysql_* functions (as they are subject to SQL Injection attacks), I can certainly provide you with a SELECT solution.

Let's assume that you have already created a DB connection, and selected a DB, with the sarting block of code that you just provided. The, we can generate a result set like so:

$sql = "SELECT JobTitle, StartDate, EndDate FROM Employment";
$result = mysql_query($sql);

This just generates the result set. The next step is to loop through the result set:

while($row = mysql_fetch_array($result))
{
  echo 'Job Title: ' . $row['JobTitle'] . '<br>';
  echo 'Start Date: ' . $row['StartDate'] . '<br>';
  echo 'End Date: ' . $row['EndDate'] . '<br>';
}

Of course, you can put any code you want inside the while loop. The while loop simply takes the result set, and iterates through each resultant row of data, retrieving each row as an associative array.

I would strongly suggest using Prepared Statements instead of mysql_* functions though. There is a tutorial on it here, by resident PHP guru Dormilich. It's a long read, and it is difficult to absorb on the first read through, but it ensures that your database is protected against SQL injection, and it also economises database usage.