...

View Full Version : PHP MySQL database creation and sorting



HawkEye147
12-14-2010, 03:16 AM
I've been teaching a PHP class at a local community center for a few weeks now. The students wanted to start doing some "useful" stuff with PHP so we started working in MySQL but I've been having a lot of trouble with MySQL. It's been a loge time since I've worked with MySQL in PHP and back when I did I was so spoiled by all the frameworks and premade libraries I had.

We've trudged our way though just making the databases in PHP but now it's time for the user to input some stuff and I'm just dead in the water with this. The idea we had was to make a database where the user would input a student's first and last name as well as graduation year and then sort the database by each of the user inputs in ascending or descending order.

So far we have been connecting to MySQL like this



<?php

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully';

/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


I've not been able to get this example started successfully at all. Any help at all would be greatly appreciated. I just want to give them the best and most up to date information that I can. While I wait for a response I will be franticly reading my old PHP and MySQL ebooks.

mlseim
12-14-2010, 04:31 AM
I assume you already have the database created?

And then, you have to create a table (or tables) and some columns.

Not sure how much you have created.

This is the basic "connect" script:


<?php
// Connect to MySQL Database
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'databasename';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
mysql_select_db($dbname) or die('Cannot select database');

// Now, you can query the database.

?>



Note, that all webhosts don't use "localhost" as the host name.
You have to consult with your webhost on how they reference that.

If you created the database, and made a table with columns (variable names),
let us know. Tell us what your table is called, and some variable names.

You access your MySQL using your webhost's control panel (MySQL admin).



.

surreal5335
12-14-2010, 05:26 AM
Some side thoughts on the development of the database... use phpMyAdmin, it comes with xamp, and any cPanel with a web hoster. creating databses, tables, and fields with it is a breeze. A 10 minute youtube video tutorial will tell you most everything you will need to know.

As for your learning material I would suggest avoiding old programming books, after a couple of years the syntax gets deprecated. I would suggest PHP.net and mysql.com. They both have very thorough and up to date documentation on the coding and pitfalls to be aware of.

HawkEye147
12-14-2010, 06:00 AM
I thank you for the responses but I guess I did a bad job of explaining what I needed. As far as the web hosting stuff goes we are using a nice little server that I installed a lamp stack on. We have not created the database yet but that's not an issue.

As far as using phpmyadmin to do this I'd rather not. We do have it installed and I showed them how to use it but I want to make a webpage that they can go to and add students. Since the next step is (as far as I was planning) is making users (with a username and password) for a website so using phpmyadmin would not really be effect for what I was hoping to do. I want all of this to be done in php.

The thing I have having trouble with is writing the script that will allow the user to come to a webpage with a html form, enter their data, then use php to add the data to mysql. Creating for form is no problem it's using php to add the data to mysql that I'm not sure about. Short of reading the whole php manual I was hoping someone could give me an example or tell me where to look in the manual. I'm also not entirely sure of how to write the sorting routines in php.

I just gave the connection script since I know there are about a thousand ways to connect to mysql with php. As far as I know using different connection methods changes the way you do things a little bit.

surreal5335
12-14-2010, 08:22 AM
To give you an idea of what to look for:

in the action portion of your form define a page to take the user to that will hold the php function for creating a new student and submitting it to the mysql database.

your form input fields will need the name="post[first_name]" attribute so PHP knows what is what during processing. Make sure your form has the attribute method="post" so post[first_name] will make sense.

After firing the fucntion holding all the processing and query strings, you will want your function to look similar to this:



function create_profile($params)
{
$connection = db_connect();
$query = sprintf("INSERT INTO profile SET
first_name = '%s',
last_name = '%s',
born = '%s',
interests = '%s',
personality = '%s',
briefdescribe = '%s',
time = NOW(),
profile_id = '%s'",
mysql_real_escape_string($params['first_name']),
mysql_real_escape_string($params['last_name']),
mysql_real_escape_string($params['born']),
mysql_real_escape_string($params['interests']),
mysql_real_escape_string($params['personality']),
mysql_real_escape_string($params['briefdescribe']),
mysql_real_escape_string($params['profile_id']));
$result = mysql_query($query);
if (!$result) return false;
else return true;
}


Something right along those lines, just need to adjust it to fit your field names and what data you want to save.

As far as PHPMyAdmin goes, I dont use it to add data into the fields. I just use it to create empty databases, tables, and fields and define the primary along with other properties for the fields... but I leave them empty, I use PHP and mysql to fill them.

mlseim
12-14-2010, 01:28 PM
I think we're saying, use phpMyAdmin to create the database, table, and columns.
Once that is done, you can then use PHP to access it ... no more phpMyAdmin.

The database, tables and columns must exist ... or you can't use PHP to query it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum