Page 1 of 1

linking php with mysql and html

Posted: Thu Aug 28, 2008 8:28 am
by barrowvian
Hey,

I’m looking to see if someone will provide me with a bit of starter code, which will enable me to extend it for my personal project. I’m new to php so don’t know that much so a couple of explanations would be helpful too!

I’ve got a basic mysql database that contain the following fields

id Course Grade
1 Computing A
2 sports B

I want to create a form that contains a checkbox for each row (apart from the ID). I’d like it so that the checkboxes interact with the database in a way that is simple to understand so that I can recreate it myself.

So obviously I’d like 2 checkboxes for course and 2 for grade. Then what I need to do is have a submit button that will process a sql query based on the selections made. For example, if the computing and the A box was checked then the command would be SELECT * FROM database WHERE course=’computing’ AND grade=’A’;
The returned value would just need to be the amount of rows selected – I believe a can do that with a SELECT COUNT statement?

All help would greatly be appreciated and would undoubtedly help me to get a better grasp of php.

Re: linking php with mysql and html

Posted: Thu Aug 28, 2008 10:37 am
by Chalks
when I have something like this to program, I generally start by creating the mysql table (which it sounds like you've done). My next step is always creating the form using regular old html (which generally results in changing my table as I think of more things). Finally, I start working on the php itself. I recommend getting as far as you can on your own. You will be using the following things for this project:

mysql_query();
empty(); // use to see if anything is in a variable
$_POST or $_GET arrays
print_r(); // use to get yourself started i.e. "print_r($_POST);"... shows you what's actually been sent by the form.

A very simple sql tutorial, easily enough to get you started.


After you have some code written, show us any problems you have, and we'll be more than happy to help further.

Re: linking php with mysql and html

Posted: Mon Sep 01, 2008 2:44 pm
by barrowvian
Right, so fat I've got this working; I know the code is messy at the moment!

Code: Select all

 
<html> 
<head> 
<title>tester_page</title> 
</head> 
 
<body bgcolor="#ffffff"> 
<form method="post"> 
    
<fieldset>
<legend>Choose a Course <br />
</legend><legend></legend><legend>
<?php   
 
$course_computing = computing;
$course_sports = sports;
 
echo"
    <input type='checkbox' name='course[]' value='$course_computing'>$course_computing
    <input type='checkbox' name='course[]' value='$course_sports'>$course_sports ";
?>    
</legend>
</fieldset>
    
<p><br />
    <input type="submit" name = "submit"> 
    <input type="reset" name = "reset"> 
</p>
 <p>&nbsp;</p>
 <p>&nbsp; </p>
</form> 
 
<?php 
if (isset($_POST['submit'])) { 
        $course = $_POST["course"]; 
        $course_count = count($course); 
           
        /*
        echo 'Courses chosen: '.$course_count.'<br /><br />'; 
        if ($course_count>0) { 
                echo 'You chose the following courses:<br />'; 
        } 
        for ($i=0; $i<$course_count; $i++) { 
                echo  ($i+1) . '- ' . $course[$i] . '<br /><br />'; 
        } 
        */
 
        echo "Check to see if COMPUTING HAS BEEN SELECTED <br />";
        if (***CHECK TO SEE IF COMPUTING IS CHECKED***){
            echo "IT WORKED! You selected" . $course;  ?????<----- JUST DISPLAY COMPUTING EVEN IF BOTH CHECK BOXES HAVE BEEN SELECTED
        }
        else
        {
            echo "IT DIDNT WORK!";
        }
        
} 
?> 
</body> 
<html>
 
If you remove the notes then the basic tasks are completed. What I want to do now is check them individually. For example, I want it to check if computing has been checked and then echo something. Then check if sports has been checked and echo something too. Ive been looking around and I'm stuck. I know its probably very simple but my brain just wont function lol

Re: linking php with mysql and html

Posted: Mon Sep 01, 2008 4:18 pm
by califdon
OK, you've made a good try, but let's take a look at some of the problems:

Are you using some kind of web authoring software? Where did that line 12 (in your post above) come from?

Lines 15-16 must have quotes around the strings, assuming that you wanted to assign those strings to those variables. With that correction, the first part of your script works, the count is correct (you can simply insert echo $course_count; to confirm that). I'm wondering why you commented out lines 39-45, since they work properly, once you fix that first issue with the quotes.

Since you will no doubt later be interfacing with a mysql database, start right now to be concerned about exploits that can be used by hackers. The first line of defense is to always run your $_POST (and $_GET) variables through mysql_real_escape_string() function before using them in your script.

Code: Select all

$course = if(isset($_POST['course'])) {
    $course=mysql_real_escape_string($_POST['course']);
} else {
    $course="";
}
I don't use arrays as names for check boxes, although that's certainly OK, but I'm not sure about how you address them, since $_POST array then is a 2- (3-?) dimensional array, since it's normally name/value pairs. Your code does work, but I think it makes it a bit harder to do what you want to do in the part that you commented out, lines 48-55, because you will have to search through the array to find if a particular value is there. What I'm accustomed to doing is naming each check box so that it is identified, then you can merely test whether that name was passed in the $_POST array. Probably not a big thing.

But what I would suggest to you is to change this example to be a bit more realistic (without over complicating it, surely). Make 3 or 4 choices and arrange the display to be more conventional, so that when it works, you will really get the feel of it. Once you have a little confidence that you understand what your Form code is doing, you should move promptly to interfacing with the database. If you don't already have a feeling for how to do this, I recommend reading http://www.tutorialspoint.com/mysql/index.htm.

Re: linking php with mysql and html

Posted: Tue Sep 02, 2008 2:36 pm
by barrowvian
Sorry, line 12 of the code is because I was messing around with DreamWeaver and fieldsets but they didnt look all that neat when I viewed the page, so I fiddled around with adding one or two more. Havent removed them because it provides enough space between the top border of the fieldset and my checkboxes.

Ive commented out lines 39-45 because they worked already - although they are helping a little bit theyre not essential to what I am doing, so when I was playing around with the layout it got in the way.

Thank you for the information about covering the exploits, last thing I want it to have an unsafe project :) Could see it happening, I finally get it working and then its gets ruined by someone else! :roll:

I understand what you're saying about not using arrays for the checkboxes to make it easier in the long run, but is there a way to search the amount of checked boxes using the array, and then selected the actual value that I want to query? For example;

Code: Select all

 
<?php   
 
$course_computing = "computing";
$course_sports = "sports";
 
echo"
    <input type='checkbox' name='course[]' value='$course_computing'>$course_computing
    <input type='checkbox' name='course[]' value='$course_sports'>$course_sports ";
?> 
 
All I want to do is something similar to my previous code. Apart from I want to check which names are selected using the array, and to then pull the value out of the array. So effectively it would leave me with a something along the lines off;

***IF BOTH BOXES HAD BEEN CHECKED***
- You have selected: 'n' courses! (which would be this part $course_count = count($course);)
- You HAVE checked computing
- You HAVE checked sports

***IF SPORTS CHECKBOX HADNT BEEN CHECKED***
- You have selected: 'n' courses! (which would be this part $course_count = count($course);)
- You HAVE checked computing
- You HAVE NOT checked sports

Becuase if I can pull out the individual values from the array then it would add abit more flexibility if I decided to expand the project to add more courses.

However, if it's not possible to do it this way, or it really is too complex, then is there any way to stil perform a count command to still count the amount of checkboxes checked without using an array?

Without using an array I have it working using;

Code: Select all

 
<?php   
 
$course_computing = "computing";
$course_sports = "sports";
 
echo"
<input type='checkbox' name='computing' value='YES'>$course_computing
<input type='checkbox' name='sports' value='YES'>$course_sports ";
 
?>    
 
Along with;

Code: Select all

 
<?php
 
if (isset($_POST['submit'])) { 
    $computing = $_POST["computing"];
    $sports = $_POST["sports"];
 
        if ($computing == 'YES') { 
                echo 'computing HAS been selected<br />'; 
        } 
        else{
                echo 'computing HAS NOT been selected<br />';
        }
        
        if ($sports == 'YES') { 
                echo 'sports HAS been selected<br />'; 
        } 
        else{
                echo 'sports HAS NOT been selected<br />';
        }
}
 
?>
 
Now fortunately that works, but if I'm wanting to do a count with it, how would I go about it using php?

Thanks

Re: linking php with mysql and html

Posted: Tue Sep 02, 2008 3:26 pm
by barrowvian
Right, I've managed to get a fair bit working on my own - I think I've got brain drain now haha.

Ive got 1 other question along with the previous one that I just asked ^^^^^

Here is what I've played around with;

Code: Select all

 
<?php
    // create database connection
    $connection = mysql_connect("localhost", "root", "passw0rd");
    if (!$connection){
        die("database connection failed: " . mysql_error());
    }
    
    // select the database to use
    $db_select = mysql_select_db("uni_project", $connection);
    if (!$db_select){
        die("database selection failed: " . mysql_error());
    }
?>
 
<html> 
<head> 
<title>tester_page</title> 
</head> 
 
<body bgcolor="#ffffff"> 
<form method="post"> 
    
<fieldset>
<legend>Choose a Course <br />
</legend><legend></legend><legend>
<?php   
 
$course_computing = "computing";
$course_sports = "sports";
/*
echo"
    <input type='checkbox' name='course[]' value='$course_computing'>$course_computing
    <input type='checkbox' name='course[]' value='$course_sports'>$course_sports ";
*/
echo"
    <input type='checkbox' name='computing' value='YES'>$course_computing
    <input type='checkbox' name='sports' value='YES'>$course_sports ";
 
?>    
</legend>
</fieldset>
    
<p><br />
    <input type="submit" name = "submit"> 
    <input type="reset" name = "reset"> 
</p>
 <p>&nbsp;</p>
 <p>&nbsp; </p>
</form> 
 
<?php 
/*if (isset($_POST['submit'])) { 
        $course = $_POST["course"]; 
        $course_count = count($course); 
           
        
        echo 'Courses chosen: '.$course_count.'<br /><br />'; 
        if ($course_count>0) { 
                echo 'You chose the following courses:<br />'; 
        } 
        for ($i=0; $i<$course_count; $i++) { 
                echo  ($i+1) . '- ' . $course[$i] . '<br /><br />'; 
        } 
        
 
        echo "Check to see if COMPUTING HAS BEEN SELECTED <br />";
        if (***CHECK TO SEE IF COMPUTING IS CHECKED***){
            echo "IT WORKED! You selected" . $course;  ?????<----- JUST DISPLAY COMPUTING EVEN IF BOTH CHECK BOXES HAVE BEEN SELECTED
        }
        else
        {
            echo "IT DIDNT WORK!";
        }
        
}*/
?> 
 
<?php
 
if (isset($_POST['submit'])) { 
    $computing = $_POST["computing"];
    $sports = $_POST["sports"];
        
    $result = mysql_query("SELECT * FROM results WHERE course='computing'", $connection);
    if (!$result){
        die("database QUERY failed: " . mysql_error());
    }
    
    $course_count = mysql_num_rows($result);
 
        if ($computing == 'YES') { 
                echo 'computing HAS been selected <br />'; 
                while ($row = mysql_fetch_array($result)){
                echo $course_count;
                }
        } 
        else{
                echo 'computing HAS NOT been selected<br />';
        }
        
        if ($sports == 'YES') { 
                echo 'sports HAS been selected<br />'; 
        } 
        else{
                echo 'sports HAS NOT been selected<br />';
        }
}
 
?>
</body> 
<html>
 
<?php
    //close the connection
    mysql_close($connection);
?>
 
 
I apologise for it being extremely messy - theres been a lot rattling around up top so Ive just commented things out so that if I made a mistake I could come back later and change it.

It's pretty much doing exactly what I want it to do at the moment, however, when I try and do the mysql_num_rows(); it displays 3 3 3 when it should really just having one 3!

Thanks again, I really appreciate you guys taking your time to point me in the right direction!

Re: linking php with mysql and html

Posted: Tue Sep 02, 2008 5:35 pm
by califdon
To your second question first: you are correctly setting the value of $course_count, but then you have inserted the echo $course_count; inside the while block, so it's dutifully repeating the value each time through the loop, which is 3 times, since there are indeed 3 rows! :)

That's a rather unusual thing to do, report the count of form boxes checked, it seems to me, but you could do it in the alternative scenario somewhat as follows:

Code: Select all

...
  $course_count=0;
  foreach ($_POST[] as $key => $value) {
    if ($key in ("computing", "sports") && &value=="YES") {
      $course_count++;
    }
  }
I haven't tried that, but I think it should work.

Re: linking php with mysql and html

Posted: Wed Sep 03, 2008 3:17 pm
by barrowvian
Thanks for the tip there Califdon regarding the code being in the wrong area, it was easily changed :)

This is it working completely with the 2 checkboxes and their if statements including sql queries;

Code: Select all

 
<?php
    // create database connection
    $connection = mysql_connect("localhost", "root", "passw0rd");
    if (!$connection){
        die("database connection failed: " . mysql_error());
    }
    
    // select the database to use
    $db_select = mysql_select_db("uni_project", $connection);
    if (!$db_select){
        die("database selection failed: " . mysql_error());
    }
?>
 
<html> 
<head> 
<title>tester_page</title> 
</head> 
 
 
<form method="post"> 
 
Please select a course to query<br /><br />
<?php   
    $course_computing = "computing";
    $course_sports = "sports";
 
    echo"<input type='checkbox' name='computing' value='YES'> $course_computing";
    echo"<input type='checkbox' name='sports' value='YES'> $course_sports";
?>    
 
<br /><br />
    
    <input type="submit" name = "submit"> 
    <input type="reset" name = "reset"> 
 
</form> 
 
<?php
if (isset($_POST['submit'])) { 
    
    $computing = mysql_real_escape_string($_POST["computing"]);
    $sports = mysql_real_escape_string($_POST["sports"]);
    $select_computing = mysql_query("SELECT * FROM results WHERE course='computing'", $connection);
    if (!$select_computing){ die("database QUERY failed: " . mysql_error());}
    $select_sports = mysql_query("SELECT * FROM results WHERE course='sports'", 
$connection);
    if (!$select_sports){ die("database QUERY failed: " . mysql_error());}
    $computing_count = mysql_num_rows($select_computing);
    $sports_count = mysql_num_rows($select_sports);
        
    if ($computing == 'YES') { 
        echo "computing HAS been selected $computing_count <br />"; 
    } 
    else{
        echo "computing HAS NOT been selected <br />";
    }
    if ($sports == 'YES') { 
        echo "sports HAS been selected $sports_count <br />"; 
    } 
    else{
        echo "sports HAS NOT been selected <br />";
    }
}
?>
 
</body> 
<html>
 
<?php
    //close the connection
    mysql_close($connection);
?>
 
 
The code is a little bit tidier so hopefully not as awkward to read. I'm going to attempt to have a drop down box now for the next section of my project and hopefully manage to merge sql queries (if thats even possible?) and put some form of validation in as I always want to have at least one checkbox selected. That should be easy enough to achieve though.

One last question though before I attempt the next couple of objectives: When I originally open the page it has the two messages for "blah HAS NOT been selected blah blah" - What I'm wanting to achieve is that when I open the page (before I use the submit button) is to display nothing but the checkboxes! Please could some (Califdon :wink: ) advise me on what is the best way about doing this? I'm going to look around the net now to see if I can find out how to do it first - but if I havent added to this post then please assume I havent found anything worth adding yet hehe!

Re: linking php with mysql and html

Posted: Wed Sep 03, 2008 4:48 pm
by califdon
I don't see how it could display any of the code that is within your if (isset($_POST['submit']) { block the first time the script is called.

But let's look at a couple of other things: You're giving the same value ("YES") to both check boxes. Usually you would give each one a different value, perhaps 1 for computing, 2 for sports. You can then do more flexible checking, like > 0 or add up all the the values (if you make them powers of 2--1, 2, 4, 8, 16..., you can tell which ones have been checked just by dividing by 2 and seeing if there's a remainder).

To make sure that at least one of them is checked, you can have it default to, say, computing, by including the "checked" attribute in its <input ... /> tag, but of course the user could still uncheck it. So you probably do want to check that in your code.

I would suggest that a better approach to learning PHP than always trying to Google for an answer, would be to just print out your script (it is always hard to read a script on your computer screen if it's more than one screen in length) and sit down and READ it, line by line, to see if you understand each line of code. Use a colored pencil to mark sections, to clarify what is going on in each block. That's the kind of thought process that will lead to your understanding.

Good luck with it.

Re: linking php with mysql and html

Posted: Sun Sep 07, 2008 4:31 pm
by barrowvian
I have the first stage completed :)

Code: Select all

 
<?php
    // create database connection
    $connection = mysql_connect("localhost", "root", "passw0rd");
    if (!$connection){
        die("database connection failed: " . mysql_error());
    }
    
    // select the database to use
    $db_select = mysql_select_db("uni_project", $connection);
    if (!$db_select){
        die("database selection failed: " . mysql_error());
    }
?>
 
<html> 
<head> 
<title>Dynamic Comparison</title> 
</head> 
 
 
<form method="post"> 
 
Please select a Course to query:<br /><br />
<?php   
    $course_computing = "computing";
    $course_sports = "sports";
 
    echo"<input type='checkbox' name='computing' value='YES'> $course_computing";
    echo"<input type='checkbox' name='sports' value='YES'> $course_sports";
?>
 
<br /><br />
 
Please select a Grade to query:<br /><br />
<select name'grade' id='grade'>
<?php 
    echo "<option>Select</option> ";
    echo "<option value='1.0'> 1.0 </option> ";
    echo "<option value='2.1'> 2.1 </option> ";
    echo "<option value='2.2'> 2.2 </option> ";
    echo "<option value='3.0'> 3.0 </option> ";
    echo "<option value='fail'> Fail </option> ";
    echo "</select> ";
?>
 
<br /><br />
 
<?php
if (isset($_POST['submit'])) { 
 
    if (!$_POST["computing"] && !$_POST["sports"]){
        echo "<b> <font color=\"#990000\"> ERROR - You need to select at least one course! </font></b><br />";
    }
    else{
        $computing = mysql_real_escape_string($_POST["computing"]);
        $sports = mysql_real_escape_string($_POST["sports"]);
        
        
        
        
        $select_computing = mysql_query("SELECT * FROM results WHERE course='computing'", $connection);
        if (!$select_computing){ die("database QUERY failed: " . mysql_error());}
        $select_sports = mysql_query("SELECT * FROM results WHERE course='sports'", $connection);
        if (!$select_sports){ die("database QUERY failed: " . mysql_error());}
        $computing_count = mysql_num_rows($select_computing);
        $sports_count = mysql_num_rows($select_sports);
        
        if ($computing == 'YES') { 
            echo "You have selected $computing_count records! <br />"; 
        } 
        else{
            echo "";
        }
        if ($sports == 'YES') { 
           echo "You have selected $sports_count records! <br />"; 
        } 
        else{
            echo "";
        }
        
    }
 
    
}
?>
    
    <br />
    <input type="submit" name = "submit"> 
    <input type="reset" name = "reset"> 
 
</form> 
</body> 
<html>
 
<?php
    //close the connection
    mysql_close($connection);
?>
 
 
Im in the process of adding a drop down list to the page which will allow the user to select which grade they want to query, as well as the course. I feel I will be using a fair few IF statements, think I'll look into using include() to put them all in one place. Once I've got one drop down menu working the way I want then it should be fairly straight forward to add a third. I have taken your advice Califdon and have printed out my documentation and feel very comfortable that I would be able to write it myself again and that I understand thoroughly what each section means :) I have created a second document which includes all of the comments for future reference too.

I've been looking into dynamic drop down menus, I think once I've got the basic's out of the way then I'll look into it more, and look at changing the SQL to accommodate it.

Will hopefully have a bit more to report with in the near future!