MySQL and PHP

1

Hi,

Do you know if it is possible fetch a MySQL record and convert each field into a PHP variable. I ask because I am building a user login system with user pages etc for my website, which you can view here. (user=test1, pass=demouser)

Any help would be great

Thanks

2 

$sql = "SELECT somecolumn FROM sometable WHERE someothercolumn = '$somePHPVariable'";
$result = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($result);
if($result)
{
     for($i=0; $i<$count; $i++)
     {
          $row = mysql_fetch_array($result);
          extract($row); 
          //creates individual php variables 
           //named after the column names in mySQL table
     }
}
3

Thank you very much. Works perfectly.

4

One thing I want to mention - Always use the function mysql_real_escape_string to sanitize any input going into the query.

5

Thanks.

I have another issue now. Do you know how to get data from a mysql, put it into a form so it can be edited, and then re submitted to the mysql. Again only editing the one record defined by a php variable?

I have worked on it all day and i’m at odds with it.

6

I’m going to assume that you know how to get the info into the form in the first place, since your original question was on retrieving. What I do is create a single function that I use for creating and updating. For example:


function form($update=false, $formValues=ARRAY())
{
     //Check to see if it is a new entry or an edit
      ($update == false) ? $submitName = "submitNew" : "submitEdit";
 
     //Check to see if the $formValues array has info
     if(count($formValues) > 0) { extract($formValues); }
 
     //Now, create the html for the form
     $form = '<form method="POST" value="'.$_SERVER['REQUEST_URI'].'"><br />';
     $form .= "Name: <input type=\"name\" value=\"$name\" /><br />";
     $form .= "Something: <input type=\"something\" value=\"$something\" /><br />";
     $form .= "Something Else: <input type=\"somethingelse\" value=\"$somethingelse\" /><br />";
     $form .= "<input type=\"submit\" value=\"Submit\" name=\"$submitName\" /></form><br />";
     echo $form;
}
 
//then, you can create two functions to handle the creation 
//of the record and the updating of the record.
 
function create()
{
     if(isset($_POST['submitNew']))
     {
          //validate input and insert into the database
     }
     else
     {
          echo "Fill out the form to create a new record";
          form();
     }
}
 
function update()
{
     if(isset($_POST['submitEdit']))
     {
          //validate input and update DB
     }
     else
     {
          //Select record from database and save data 
          //into an array named $row for instance
          form(true, $row);
     }
}