Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 how does mysql/php work with server/database/internet... and everthing else?
Index -> PHP
Goto page 1, 2  Next
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
chopperdudes




PostPosted: Tue Dec 08, 2009 12:10 am   Post subject: how does mysql/php work with server/database/internet... and everthing else?

for the summative project, our teacher has assigned us (3 of us) to design a website application where one can book parent-teacher interviews. and frankly, our teacher hopes to put this project into actual use (and so he decided to assign this project to 3 of the most competent coding students in his class who apparently have never seen mysql or php). we've been looking at some of the very basic tutorials/concepts behind this, however, none of us have any experience with any server-database-related stuff. we're competent coders in terms of algorithms and the likes, however, as previously mentioned, we're really lost right now...

can any1 point out any good starting points/clear up some fundamental concepts behind server-database applications?

some of the more specific questions we have are:
how does the connection actually work if it's an application on the school website where parents can access/modify the information in the database?
where is the database stored, and how does this relate to the server (basically concepts behind databases and servers)
we've read some tutorials on mysql, it involves a shell from where commands are entered and data in the database are fetched/changed, how can this be achieved with an online application where the users do not enter commands into the shell?
how does mysql and php work together to provide the user/database interaction?

and really, we need as much info as possible seeing how we got this assigned today and literally have 3 weeks to complete it (aka getting it actually connected and on the school website).

thanks a lot!
Sponsor
Sponsor
Sponsor
sponsor
rdrake




PostPosted: Tue Dec 08, 2009 1:06 am   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

Moved to PHP.

Your PHP code accesses the database running on the same machine. It acts as an interface to the database. The user indirectly modifies the database through your code.

The database is stored wherever. It really doesn't matter to you.

Your PHP code makes use of the PHP-MySQLi interface to execute the "commands" AKA SQL.

ibid.
DemonWasp




PostPosted: Tue Dec 08, 2009 4:29 am   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

A brief overview:

The Server Hardware (computer): the server that "hosts" the "application". In general, likely to be a Linux machine running Apache, often with PHP and MySQL (this is collectively known as the LAMP stack).

The Client Hardware (computer): the computers that your users look at your webpage from. They're not really all that special. They have a web browser.

The Database: this knowledgeable but reclusive resident lives in the server. He hates interruptions though, so he generally won't respond to anyone who doesn't also live in the server (connections from other machines disabled). He stores all the application-space information, like user names, salted passwords, join dates, appointments and the like. Your server always asks him for information and to record updates whenever anything interesting happens; when he replies he tends to speak very tersely and in code (SQL responses).

The Server Application (backend): this talkative chap also lives on the server, and where Database is a recluse, Server is outgoing. In fact, Server is such a social animal that he waits by the phone day and night for other people to call him. Whenever someone calls him, Server eagerly responds with whatever information they ask for, though he rephrases (formats nicely) the information he gets from Database.

The Client Application: comes in many different flavours (IE, Firefox, Safari, Opera...), but all behave fairly similarly. They call up server and after getting the pleasantries out of the way (HTTP connection headers), ask Server for some valuable information. Server asks database and returns a response. Client continues the conversation, getting new information each time before eventually becoming bored with Server's chattering and disconnecting.


In this analogy, the "call" that takes place is an HTTP session; connecting is initiating the session and disconnecting is terminating it.

You can talk directly to Database using the mysql command line utility, but he's not much of a talker and it's usually easier to have Server talk to him for you. Clients should NEVER be able to talk directly to Database. Database keeps track of his own library of information and HATES when other people touch it - usually, this makes him so mad he just stops working; best to just leave him to handle it himself (don't touch the database files...seriously).


In comparison to a standard application, a database-using application just pulls its information from new locations. Consider your first few applications. They likely read data from a specially-formatted file on the hard disk, putting it into some format they understood in memory. This is slightly different: each time you need to look at information, you'll ask Database to do the hard bits for you and just use that. This usually plays out as:

1. User clicks on "Submit" button. (GUI interaction)
2. Client goes "Oh hell, I guess I'll ask that Server guy what he thinks." (HTTP request)
3. Server goes "Oh someone loves me! I'll get that information right away...hey database, can you look up who did this thing on that day, with those other people? That'd be greaaaaat." (SQL request)
4. Database shoots Server a dirty look, shuffles some papers about and tells Server "Tim". (SQL response)
5. Server tells client "Tim did X with Y and Z because of Q" (usually, by sending a new page with this message to the Client - HTTP response).
6. Client shows this to the user (usually, by drawing the new page from the Server - GUI response).


Now that I look back on this, that's kind of a stupid explanation. Hopefully I didn't just confuse you more. Good luck with that timeline: 3 weeks is a little short for professional programmers to write an application like that, assuming you need to do any formal testing at all.
chopperdudes




PostPosted: Mon Dec 14, 2009 11:49 pm   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

thanks a lot! despite how fairy-tale like it sounded, your explanation is very clear and helped a lot. with a bit more research, we are now able to setup a local host within our computers and have started experimenting with php codes. we haven't done much yet, but will do so over the winter break.

now we face another problem. in setting up the database with mysql (we're using phpmyAdmine to do it), is it possible to have something like classes in OOP? for example, have a student class, which will include phone, address, parents, parent emails, student number, etc. i know that these can just be put into columns, however, the trouble we face now is, what if an attribute of a student is actually a table on its own? i.e. his/her timetable? in programming terms this would mean that you would have a 2D array field in the class student, but how would you go about implementing this with mysql tables?

and, our plan so far (please add in suggestions):
each student is identified with a parent, and therefore the parent's email address.
the software will be up a month prior to the actual parent-teacher interview date.
the email addresses are sorted, duplicates are ignored (i.e. siblings with same parent).
a random code is generated for each email address, and an email containing the specific code will be sent to the email address (aka the parent)
a login system will use the parents' email address and code.
upon login, a parent can select child(ren), and then his/her teachers.
a schedule table will pop up, and appointments can be booked that way.
after booking an appointment, a confirmation will be sent to the email, and an activation within 24 hours will be required, or else the booking is nullified.
on the teacher side, the teachers (with their own account and privileges), will be able to view their own booking.
they will also be able to reserve a booking (in case a parent does not have email, and he/she will have to contact the teacher directly).
anything else, the admin will have to be contacted.

other cool features which should be implemented are multiple languages (i.e. for the GUI buttons)
flash based website
email notification prior to actual date,
and other user-friendly features.

any more suggestions are welcomed... BUT, we must figure out how to store a list of something equivalent to a class of objects...
DemonWasp




PostPosted: Tue Dec 15, 2009 12:07 am   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

There are a lot of solutions for translating classes to database and back. See this article.


Generally, you would use FOREIGN KEYS. These are like pointers to object instances, in that they point to a record in another (foreign) table. Your foreign key type would usually be the same as the PRIMARY KEY on the target table, usually an unsigned integer.


If neither of those solves your problem, can you clarify what you need?
chopperdudes




PostPosted: Tue Dec 15, 2009 12:23 am   Post subject: Re: how does mysql/php work with server/database/internet... and everthing else?

thanks for the quick reply, the first one doesn't seem to be our issue right now, although that would be good practice. the foreign key concept seems promising so i would undoubtedly do more research on that. but i guess just to restate/clarify what i'm asking, i guess what i want to model is something like this:

Java:


class student
{
String firstName, midName, lastName;
// all other personal info...
timeSlot [][] timeTable;
}

class timeSlot
{
String teacher, courseCode, course;
int roomNumber;
}

and basically, an array of student

student [] students; is the data structure i want to mimic.
DtY




PostPosted: Tue Dec 15, 2009 7:57 am   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

For doing queries, make sure you use mysqli with prepared statements (instead of old php mysql library).
At the very least, make sure you know about SQL Injection.

The idea behind prepared statements is that you send the actual data separately from the query. For example, instead of

SELECT * FROM `users` WHERE username="$username" AND passwd="$passwd"

Which is vulnerable to SQL injection (unless $username and $passwd were sanitized (process escaping the characters special in SQL to protect from SQL ibjection)) you would do:

SELECT * FROM `users` WHERE username=? AND passwd=?

And then pass along the values for those (iirc, in php/mysqli you use the function mysqli_bind_param to do that). Since this does not send the data as part of the query, it's safe from SQL injection.
This also allows you to call the same query over and over with new variables while only sending the new data, and not the original query (which isn't a big deal, since the data most often outweighs the query).
DemonWasp




PostPosted: Tue Dec 15, 2009 10:02 am   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

You can almost certainly model a 2D array as a 1D array (remember, that's how it's done in RAM). To visualize, take a chess board and start numbering squares from the top left, heading right. When you get to the right side (square 8) start back at the left side on the next row (square 9). The formula for the 1D index for any 2D (x,y) coordinate pair is index = y * number-of-columns + x.

Then you have (forgive my half-remembered SQL syntax):

code:

TABLE Student (
  ID UNSIGNED INT PRIMARY KEY AUTO INCREMENT
);

TABLE TimeSlot (
  StudentID UNSIGNED INT,
  Index UNSIGNED INT,
  Section UNSIGNED INT,

  FOREIGN KEY ( StudentID ) REFERENCES Student.ID,
  FOREIGN KEY ( ClassID ) REFERENCES Section.ID
);


This sets up a Student class which can have multiple TimeSlots associated with it, each of which references the student belonging to it as well as the section (one class of a course) that the student attends during that time. The section could, conceivably, have a reference to the teacher (name, office hours...), a reference to the course (which has a course description, prerequisite list...).

You would select these as follows:
code:

SELECT * FROM TimeSlots where StudentID = '<put student ID here...>' ORDER BY Index


This might seem backwards at first, but you'll get used to it.
chopperdudes




PostPosted: Sun Dec 20, 2009 9:16 pm   Post subject: Re: how does mysql/php work with server/database/internet... and everthing else?

thank you guys alot, we've been working on it lately, not much progress as each of us have around 3 final projects to do during the xmas break. however, i did experiment with it abit, and find ways to achieve some results. however, i really have no clue what the conventions for php are, and i don't mean the naming of the variables and stuff. for example, i'm used to working with arrays, so for me it would be natural to read the database (or part of it) into an array, and then manipulating that and outputting it.

the SQL injection is rather interesting, i used the function mysql_real_escape_string for now, as the prepared statements are abit overwhelming.

also, thanks demonwasp for point that out, however, i think that would be be rather difficult for us to learn over such a short period of time. instead, we so far decided to store the 4 courses of a student (per semester) as individual columns in the database, and each course will be a string in a special format which will allow us to get the various information associated with a course.


below are some of the code i manage to come up with after learning the basics:

php:

<html>
<head>
<title>PHP Test</title>
</head>
<body>


<?php
include("db.php");
$connection = mysql_connect($server, $username, $password) or die("Could not connect to server!");
$db = mysql_select_db($database, $connection) or die("Can't select database!");


function readData ($q)
{
        $result = mysql_query($q) or die("Could not execute query!");
        for ($i = 0; $temp = mysql_fetch_assoc($result); $i++)
                $table [$i] = $temp;
        return $table;
}

function randomCode()
{
        $length = 6;   
        $randomCode = "";
        for ($i = 0; $i < $length; $i++)
                if (rand(0,1) == 0)
                        $randomCode .= rand(0,9);
                else
                        $randomCode .= chr(rand(65, 90));
       
        return $randomCode;
}

function setRandomCode()
{
        mysql_query("UPDATE students SET random_code = ''");
        $table = readData ("SELECT * FROM students ORDER BY email_address");
        $numberOfStudents = count($table);
        foreach ($table as $row){
                if (strcasecmp ($row['random_code'],"") == 0)
                        mysql_query("UPDATE students SET random_code = '".randomCode().
                        "' WHERE email_address = '".$row['email_address']."'");
        }
}


setRandomCode();

$studentData = readData ("SELECT * FROM students ORDER BY email_address");
$numberOfStudents = count($studentData);

foreach ($studentData as $row){
        foreach ($row as $value)
                echo "$value\n";
        echo "<br/>";
}


?>

</body>
</html>


the most basic login page:

php:

login.php:

<html>
<head>
<title>PHP Login</title>
</head>
<body>

<form action="welcome.php" method="post">
email: <input type="text" name="email" />
randomCode: <input type="password" name="randomCode" />
<input type="submit" />
</form>

</body>
</html>


welcome.php:

<html>
<head>
<title>PHP Welcome</title>
</head>
<body>

<?php
include("db.php");
$connection = mysql_connect($server, $username, $password) or die("Could not connect to server!");
$db = mysql_select_db($database, $connection) or die("Can't select database!");


function readData ($q)
{
        $result = mysql_query($q) or die("Could not execute query!");
        for ($i = 0; $temp = mysql_fetch_assoc($result); $i++)
                $table [$i] = $temp;
        return $table;
}



$email = $_POST["email"];
$password = $_POST["randomCode"];


$info = readData ("SELECT * FROM students WHERE email_address = '".mysql_real_escape_string($email)."'");

if (strcasecmp ($info[0]['random_code'], $password)==0)
{
        foreach ($info as $row){
                foreach ($row as $value)
                        echo $value." ";
                echo "<br/>";
        }
}
else
{
echo "incorrect username or password";
}

?>

</body>
</html>


any suggestions are highly valuable to us, thanks guys!
DtY




PostPosted: Sun Dec 20, 2009 9:43 pm   Post subject: Re: how does mysql/php work with server/database/internet... and everthing else?

Yeah, I understand not wanting to use prepared statements, there's a learning curve, but once you learn them, you'll be glad.
Definitely not something you need to use for your first project though.

Just some small tips;

php:
$result = mysql_query($q) or die("Could not execute query!");

Personally, I only bother the or die() on the connection and database selection, because I find it just gets in the way, and if you are able to connect to the database, executing queries should go fine.
If you want to be extra safe though, go for it

[syntax="php"[$table [$i] = $temp;[/syntax]
Where did you declare table? There should be a $table = array(); somewhere in there.
Also, a neat feature in php is $table[] = 5; will append 5 to the end of table.
So, instead of:
php:
        for ($i = 0; $temp = mysql_fetch_assoc($result); $i++)
                $table [$i] = $temp;

You can do:
php:
while ($temp = mysql_fetch_assoc($result)) { $table[] = $temp; }


php:
include("db.php");

PHP has four functions to include another file, and it's good to understand them all, they are: include(), require(), include_once() and require_once().
The two that end with _once will make sure to not include that file more than once, it will do nothing if that file has already been included in the current file. When the file is not found, the two starting with include will raise a warning, and continue, but the two starting with require will raise an error, and stop processing the page.
Since this file includes the database information, if it cannot be included, the rest of the page will not work, so it makes sense to use require() or require_once() in this situation.
For the more trivial pages, like a footer, where the page will still work if that file were to not be included, it makes sense to use include() or include_once().

php:
$connection = mysql_connect($server, $username, $password) or die("Could not connect to server!");
$db = mysql_select_db($database, $connection) or die("Can't select database!");

It's probably best to do this in db.php, so you're not replicating it on every single page (unless some pages need to do something different, that includes db.php, but does not connect to the database, in which case it would be best to put that in a function inside db.php).

php:
$info = readData ("SELECT * FROM students WHERE email_address = '".mysql_real_escape_string($email)."'");

Personally, I prefer to use interpolation to build strings like this, but it's a matter of preference.
(If you haven't learned about string interpolation yet)
chopperdudes




PostPosted: Sun Dec 20, 2009 10:10 pm   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

oh hmm thanks! these are the things i was talking about. somehow by experimenting, i found my $table to work. and this was because all over variables does not need to be declared, so i assumed an array would be the same. and apparently my weird method of doing it (specifying an increasing index each time) works. i will definitely change that, and thanks for the tips.

are there any other bad coding habits in there that needs to be caught early on to prevent using it throughout this whole project? and i'm afraid that this project, even though it's the first time we've ever done anything with php/mysql, our teacher has high hopes of actually putting it to use on the school website. so it has to be at least "semi-professional".
chopperdudes




PostPosted: Sun Dec 20, 2009 10:43 pm   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

also, is there a free editor out there that will indent your php code for you? and also, do you just link pages together using the html ?
DtY




PostPosted: Sun Dec 20, 2009 10:48 pm   Post subject: Re: RE:how does mysql/php work with server/database/internet... and everthing else?

chopperdudes @ Sun Dec 20, 2009 10:10 pm wrote:
oh hmm thanks! these are the things i was talking about. somehow by experimenting, i found my $table to work. and this was because all over variables does not need to be declared, so i assumed an array would be the same. and apparently my weird method of doing it (specifying an increasing index each time) works. i will definitely change that, and thanks for the tips.

Your method works fine because you can also create new items in an array by assigning to an element that doesn't exist;
$arr[5] = 3;
will set index five to three, whether or not the array was long enough. It wont create indexed 0, 1, 2, 3 or 4 though.
Using a while loop (imo) just looks clearer
chopperdudes




PostPosted: Sun Dec 27, 2009 7:47 pm   Post subject: Re: how does mysql/php work with server/database/internet... and everthing else?

okay we've encountered a very retarded problem and we've been stuck at it for a day now already. we want to pass an element of a 2D array from php to flash. we tried making up all kinds of 2D arrays, and we can pass anything to flash EXCEPT what we needed.

here's what i mean:
code:

function readData ($q)
{
        $table = array();
        $result = mysql_query($q) or die("Could not execute query!");
        while ($temp = mysql_fetch_assoc($result))
                $table [] = $temp;
        return $table;
}

$info = readData ("SELECT * FROM students WHERE email_address = '".mysql_real_escape_string($email)."'");
echo "&parentOne=".$info[0]['parent1_first_name'];


the above does not work.
however, if we just randomly made up some array, i.e.

code:

$omg [0]['hi'] = "hello";
echo "&parentOne=".$omg [0]['hi'];

then it works.

is the data type in the $info array from readData() different? what might be the problem?
here's the action script 2 code

code:

lvOut = new LoadVars();//create lv object
lvIn = new LoadVars();//create lv object
lvIn.onLoad = function(success) {
        if (success) {
                _root.parent1M.parent1Name.text = lvIn.parentOne;
        }else{
                _root.parent1M.parent1Name.text = "OMG";
        }
};
lvOut.sendAndLoad("../test/welcome.php",lvIn,"POST");


we also tried the imploding method and passing on the whole array and splitting the array up in flash, no success that way either.
DtY




PostPosted: Sun Dec 27, 2009 8:22 pm   Post subject: RE:how does mysql/php work with server/database/internet... and everthing else?

I'm not entirely sure what you're trying to do, but I think you're using flash to grab a PHP file, and the PHP file will pass along data? (Btw, if this is what you're doing, this is called AJAX).

All I can think of that could be causing a problem is that that query is actually not returning any results. Since you're posting, figuring out if this is the case can be a bit difficult.

If you were passing the parameters from the url (ie. the query string (somehting.php?query_string)) you could just go to the page in a browser and seeing what' seeing what's going on. You can still do this with post, but not just by going to the page in the browser (the easiest way would be to just make an html form with the same variables).

Before doing that though, you should look at the webserver's error log. Where it is depends on the server and the platform, but if it's Linux it would most likely be found in /var/log/(server)/error.log (But knowing how most schools work, it's probably on IIS, and I have no idea where the logs would be found).
Display posts from previous:   
   Index -> PHP
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 2  [ 16 Posts ]
Goto page 1, 2  Next
Jump to:   


Style:  
Search: