Display Detail Page with Select box

My idea is to drop down a menu which contains user ID, once user select user ID, it will display more information of the user ID.

Firstly, I made a path which connects to MYSQL database.

include'include/conn.php';

Secondly, I create form :

form method="POST" action="main.php?userID=$userID"

and PHP code inside:



Then a function code that retrieves all data belonging to the user ID that user select.

$display = 'SELECT * FROM user WHERE userID ='.$_GET['userID'];
$result = mysql_query($display, $db)or die(mysql_error($db));

$row = mysql_fetch_assoc($result);
$userID = $row['userID'];
$userName = $row['userName'];
$password = $row['password'];
$fullname = $row['fullName'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$city = $row['city'];
$country = $row['country'];

Display Information:




Testing the result:



I try selecting a user ID to display all data belonging to the user ID:



All errors are viewed as SQL error.

I posted my query on a forum to resolve the problems:



There was a guy in the form support me a hint that is why the problem occurs:




The hint helps me solve it out. I figured out the error caused by

form method="POST" action="main.php?userID=$userID"

In this case, $userID is not a variable, it have to be a string of variable (userID = $userID)

I used isset() function and changed the code.

ISSET() =>determine if a variable is set and is not NULL

If a variable has been unset with unset(), it will no longer be set. isset() will return FALSE if testing a variable that has been set to NULL. Also note that a NULL byte ("\0") is not equivalent to the PHP NULL constant.

If multiple parameters are supplied then isset() will return TRUE only if all of the parameters are set. Evaluation goes from left to right and stops as soon as an unset variable is encountered.

isset() only works with variables as passing anything else will result in a parse error. For checking if constants are set use the defined() function.

When using isset() on inaccessible object properties, the __isset overloading method will be called, if declared.

In the select option, I name it as "selectBox"




Using isset() function. When a user click on submit button, userID will be called.



Display Information



Testing the result:

I have selected user ID as number 6:

Working with INSERT statement

After creating a database, now I have to insert all data into table attributes.

First of all, I create a page called test.php.
This page will contains a html form that will passes all variables to insert.php

Embedding a php connection page by code

include'include/conn.php';

Then I simply create form like the picture:



with a CSS file containing :

input
{
background-color: #99ccff;
color: black;
font-family: arial, verdana, ms sans serif;
font-weight: bold;
font-size: 12pt
}
.button
{
background-color: blue;
font-family: verdana;
border: #000000 1px solid;
font-size: 12px;
color: aqua
}

Input to make a input cell colored with blue and black font. Beside that, a class will be name inside the input button code so that ".button" can change.

Final Step is to create insert.php file that gets the connection page and all variables from test.php

include'include/conn.php';

mysql_select_db('user',$db)or die(mysq_error($db));

$username = "$_POST[username]";
$pass = "$_POST[pass]";
$email = "$_POST[email]";
$fullname = "$_POST[fullname]";
$address1 = "$_POST[address1]";
$address2 = "$_POST[address2]";
$city = "$_POST[city]";
$country = "$_POST[country]";

$insert = 'INSERT INTO user(userName,password,email,fullName,address1,address2,city,country) VALUES ("$username", "$pass", "$email", "$fullname", "$address1", "$address2", "$city", "$country")';

$result = mysql_query($insert,$db)or die(mysql_error($db));

Then open and enclose html tag with table inside to view the result of insert function.

table width="100%"
tr
th>User ID < /th th>User NamePasswordFull NameAddressCityCountry

then input a script below inside open and enclose php tag

$display = 'SELECT * FROM user';

$reDisplay = mysql_query($display,$db)or die(mysql_error($db));

while ($row = mysql_fetch_assoc($reDisplay))
{
echo '';
foreach ($row as $value)
{
echo(''.$value.'');
}
echo '';
}

Finally, I preview the page to insert

test.php



and insert.php



The problem is that all variables are inserted into database 'user' instead of their data values.

I posted my question in a forum of hotrolaptrinh.com



and get a reply :



so the problem is double and single quote (" and ') that I confused in coding.

The code should be changed like:

$username = $_POST['username'];
$pass = $_POST['pass'];
$email = $_POST['email'];
$fullname = $_POST['fullname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$country = $_POST['country'];

$insert = "INSERT INTO
user(userName,password,email,fullName,address1,address2,city,country)
VALUES
(' ".$username." ',
' ".$pass." ',
' ".$email." ',
' ".$fullname." ',
' ".$address1." ',
' ".$address2." ',
' ".$city." ',
' ".$country." ')";

$result = mysql_query($insert,$db)or die(mysql_error($db));

Now I test the code and get the successful result:



All data are filled inside different table attributes.

Working between SQL and PHP : CREATE

On the first line, I include the connection page

include'include/conn.php';

Next, I make SQL to make a query:

//CREATE THE MAIN DATABASE AND RECOGNIZE THE DATABASE IF IT ALREADY EXIST
$database = 'CREATE DATABASE IF NOT EXISTS user';
mysql_query($database,$db) or die (mysql_error($db));

Choosing a database that is just created, named user.

//Make sure the recently created database is the active one
mysql_select_db ('user', $db) or die(mysql_error($db));

Then make a query to create table attributes:
//CREATE THE USER TABLE
$query =' CREATE TABLE user(
userID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
userName VARCHAR (255),
password VARCHAR (255),
email VARCHAR (255),
fullName VARCHAR (255),
address1 VARCHAR (255),
address2 VARCHAR (255),
city VARCHAR (255),
country VARCHAR (255),

PRIMARY KEY (userID)
)ENGINE = MyISAM';

mysql_query($query, $db) or die (mysql_error($db));

echo 'Database is created';

So now the database user is created with its all attributes.

Working between SQL and PHP : Displaying data by SELECT syntax

Firstly, I create a page called test.php which has some code following:

// This is to connect to localhost server, it can be domain name hoặc IP address
$server = " localhost";

//account user name which logins MySQL server, is root
$userName = " root";

//account password to login mysql server
$password ="";

//Name of chosen database to be interacted with, is test
$dbName = "test";

//Inputting server, username and password into mysql_connect function
$conn = mysql_connect($server, $username, $password);

with $conn is a connection which is done by mysql_connect statement.
I use IF statement to report error if it occur in the connection

if ( !$conn ) {
//Cant connect to mysql server, exit and report error
die("Cant connect to Mysql server");
} //end if

//choose a database for interaction by mysql_select_db statement
mysql_select_db($dbName, $conn);
or die("Can not choose a CSDL Database: ".mysql_error($conn));

Finally, close the connection with mysql_close statement.

//Close the connection
mysql_close($conn);

There are 2 ways to display data from MySQL database by mysql_fetch_row() statement or mysql_fetch_assoc() statement

In first practice, I use mysql_fetch_row()

so I have full code on database connection and retrieve data from MySQL database

$SERVER = "localhost";
$USERNAME = "root";
$PASSWORD = "";
$DBNAME = "test";

$conn = mysql_connect($SERVER, $USERNAME, $PASSWORD);
if ( !$conn ) {
//Cant not connect, exit and report error
die("Cant connect to MySQL server: ".mysql_error($conn));
} //end if

//choose database to work with
mysql_select_db($DBNAME, $conn)
or die("Cant select CSDL database: ".mysql_error($conn));

$sql = "SELECT * FROM groupCategory";
$result = mysql_query($sql, $conn);
if ( !$result )
die("Cant do your query: ".mysql_error($conn));

echo "Quantity of rows: ".mysql_num_rows($result)."
\n";

while ( $row = mysql_fetch_row()($result) ) {

        echo "gID = ".$row[0]."
\n";
echo "gName = ".$row[1]."
\n";
echo "gDescription = ".$row[2]."
\n";} //end while

//We should free the memory after getting rows from SELECT statement
mysql_free_result($result);

//đóng kết nối
mysql_close($conn);

A full code in my PHP editor looks like:



In my second practice, I use mysql_fetch_assoc() statement

$SERVER = "localhost";
$USERNAME = "root";
$PASSWORD = "";
$DBNAME = "test";

$conn = mysql_connect($SERVER, $USERNAME, $PASSWORD);
if ( !$conn ) {
//Cant not connect, exit and report error
die("Cant connect to MySQL server: ".mysql_error($conn));
} //end if

//choose database to work with
mysql_select_db($DBNAME, $conn)
or die("Cant select CSDL database: ".mysql_error($conn));

$sql = "SELECT * FROM groupCategory";
$result = mysql_query($sql, $conn);
if ( !$result )
die("Cant do your query: ".mysql_error($conn));

echo "Quantity of rows: ".mysql_num_rows($result)."\n";

while ( $row = mysql_fetch_assoc($result) ) {
echo "gID = ".$row['gID']."
\n";
echo "gName = ".$row['gName']."
\n";
echo "gDescription = ".$row['gDescription']."
\n";
} //end while
//We should free the memory after getting rows from SELECT statement
mysql_free_result($result);

//đóng kết nối
mysql_close($conn);

Screen of full code:



Notice:

mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0. (Hàm mysql_fetch_row() sẽ trả về 1 array mà phần tử thứ [0] sẽ tương ứng với cột đầu tiên của table, phần tử thứ [1] sẽ tương ứng với cột thứ hai của table)

mysql_fetch_assoc() fetches a result row as an associative array.
If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, we either need to access the result with numeric indices by using mysql_fetch_row() or add alias names
Note: Performance
An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value

Both mysql_fetch_row() statement and mysql_fetch_assoc() statement give the same result:



or another way to view data by the code below



The result will be viewed:



However, the table will not be in order, to fix this problem, I replace :



The result now looks much nicer

Making a connection between PHP and MySQL database

Firstly, I create a page called connection.php and input some code:

$connection => a string to be called for the connection. This string can be stated by any name such as db, connection,...

mysql_connect() requires a host name, user name and password (in that order).

Add a die() function to the mysql_connect() line to cause the script to end and a message to display if the connection fails.
Within the die() function, use the mysql_error() function in order to print out the exact error sent by MySQL.

the full code will be input like this:


However, the connection error is shown:



the error cause the problem is mysql_connect function. It lacks of @ so we can fix like



the problem still happen:



This error mean I use wrong user name and pass in order to connect to MySQL Database server.

Other method to connect

Creating 4 variables such as :

// This is to connect to localhost server, it can be domain name hoặc IP address
$SERVER = " localhost";

//account user name which logins MySQL server, is root
$USERNAME = " root";

//account password to login mysql server
$PASSWORD="";

With MySQL syntax:

mysql_connect($server_address, $username, $password)

I have some lines of code :

Using PHPMyAdmin to create a database and SQL

Creating database in MySQL server by PhpMyAdmin

I name this database as supermarket.



the result is successfully done like this



We can use create table functionality from phpMyAdmin in order to create a new table in the database.

Creating GROUP CATEGORY Table and its data:

Using PHPMYADMIN to create 3 fields for group category table:



Group Category table with creating table SQL code and its result:



Insert data:








Data Dictionary




Creating CATEGORY table and its data.





Create "category" table by SQL query.



Table Result



Insert Data:












Data Dictionary: