display data of database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

display data of database

Post by terrenuit »

Hi, my scripte runs for display the data of my database, but Wamp server indicated 2 lines have errors, someone can explain me ?
this 2 lines have problem:
$rows = mysql_fetch_assoc($sql);

echo 'id: ' . $row[$id] . 'name: '. $row[$name] . 'image: '. $row[$image] . 'e-mail: '. $row[$e_mail];

Code: Select all

<?php
 mysql_connect("localhost","root","");
 mysql_select_db("bosse");
 $sql = mysql_query("SELECT * FROM trial ORDER BY id ASC");

 $id = 'trial_id';
 $name = 'nom';
 $image = 'photo';
 $e_mail = 'mail';
 $rows = mysql_fetch_assoc($sql);

 echo 'id: ' . $row[$id] . 'name: '. $row[$name] . 'image: '. $row[$image] . 'e-mail: '. $row[$e_mail];
 ?>
Last edited by Celauran on Tue Jul 08, 2014 2:40 pm, edited 1 time in total.
Reason: Please wrap your PHP code in syntax tags to keep things legible.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: display data of database

Post by Celauran »

You'd need to at least tell us what the errors say.
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

this line: $rows = mysql_fetch_assoc($sql);
the error indicate:
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wampserver32\www\good\index.php on line 12

this line:
echo 'id: ' . $row[$id] . 'name: '. $row[$name] . 'image: '. $row[$image] . 'e-mail: '. $row[$e_mail];
the error indicate:
Notice: Undefined variable: row in C:\wampserver32\www\good\index.php on line 14
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: display data of database

Post by Celauran »

That means $sql is false, rather than a MySQL resource, which means there's something wrong with the query. Check the output of mysql_error().
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

in fact, I copied this scripte from a tutorial which is OK as his video,
I only changed the database name, why my scripte is not OK ?
do you think the problem is my database ? may be my database is not well done so that MYSQL cannot find the resource of my database ?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: display data of database

Post by Celauran »

I can't guess. Check what mysql_error() says. I'll tell you now that the tutorial is outdated; mysql_ functions have been deprecated, will be removed in upcoming versions of PHP, and really shouldn't be used. Check out mysqli or, better, PDO. That's for later, though. First, let's make sure we can get PHP talking to your database and go from there.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: display data of database

Post by mikosiko »

Code: Select all

<?php
 mysql_connect("localhost","root","");
 mysql_select_db("bosse");
 $sql = mysql_query("SELECT * FROM trial ORDER BY id ASC");

 $id = 'trial_id';
 $name = 'nom';
 $image = 'photo';
 $e_mail = 'mail';
 $rows = mysql_fetch_assoc($sql);

 echo 'id: ' . $row[$id] . 'name: '. $row[$name] . 'image: '. $row[$image] . 'e-mail: '. $row[$e_mail];
 ?>
- most likely your query is failing because you don't have a field named "id" (judging for the subsequent lines) instead the right field seems to be "trial_id".
- $row is not defined either... you are declaring it as "$rows" (plural) previously
- why are you declaring additional variables?.... you can use the originals directly to display the values ... like $rows['trial_id'], $rows['nom'], etc.
- are you interested ONLY in the first record of your result set?... you are using mysql_fetch_assoc() outside of a loop, therefore you will get only the first record.
- Finally.... you realize that mysql API has been deprecated since long time ago and could be removed sooner or later?... you should be using PDO o mysqli API instead.
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

I have no any idea of the PDO MYSQL, I will study it,
but the Wamp server is compatible of PDO MYSQL ?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: display data of database

Post by mikosiko »

look in your php.ini for the line

extension=php_pdo_mysql.dll .... if it looks like this --> ;extension=php_pdo_mysql.dll then suppress the semicolon, save it and restart Apache

be sure first that php_pdo_mysql.dll is present in your "extension" directory in the wamp server

phpinfo() should show you the enabled extensions too.

Here you can read and learn how to use PDO http://www.php.net/manual/en/book.pdo.php
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: display data of database

Post by Pazuzu156 »

Here is a simple DB class used as a simple PDO wrapper.

Comes with easy options for CRUD interaction and feels similar to Eloquent DB system.

Class:

Code: Select all

<?php

class DB
{
	private static $_instance = null;

	private $_pdo,
			$_query,
			$_results,
			$_error = false,
			$_count = 0;

	public function __construct($host, $username, $password, $db)
	{
		try
		{
			$this->_pdo = new PDO('mysql:host=' . $this->_host . ';dbname=' . $this->_db, $this->_username, $this->_password);
		}
		catch(PDOException $ex)
		{
			die($ex->getMessage());
		}
	}

	public static function getInstance()
	{
		if(!isset(self::$_instance))
			self::$_instance = new DB();

		return self::$_instance;
	}

	public function query($sql, $params = array())
	{
		$this->_error = false;
		if($this->_quert = $this->_pdo->prepare($sql))
		{
			if(count($params))
			{
				$x = 1;
				foreach($params as $param)
				{
					$this->_query->bindValue($x, $param);
					$x++;
				}
			}

			if($this->_query->execute())
			{
				$this->_results = $this->_query->fetch(PDO::FETCH_OBJ);
				$this->_count = $this->_query->rowCount();
			}
			else
			{
				$this->_error = true;
			}
		}

		return $this;
	}

	public function action($action, $table, $where = array(), $order = "ASC")
	{
		if(count($where) === 3)
		{
			$ops = array('=', '>', '<', '>=', '<=');

			$field = $where[0];
			$op = $where[1];
			$val = $where[2];

			if(in_array($op, $ops))
			{
				$sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";
				if(!$this->query($sql, array($val))->error())
				{
					return $this;
				}
			}
		}

		if(count($where) === 0)
		{
			$sql = "{$action} FROM {$table}";
			if(!$this->query($sql)->error())
			{
				return $this;
			}
		}

		return false;
	}

	public function insert($table, $fields = array())
	{
		if(count($fields))
		{
			$keys = array_keys($fields);
			$values = '';
			$x = 1;

			foreach($fields as $field)
			{
				$values .= "?";
				if($x < count($fields))
				{
					$values .= ", ";
				}
				$x++;
			}

			$sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES ({$values})";

			if(!$this->_query($sql, $fields)->error())
			{
				return true;
			}
		}

		return false;
	}

	public function update($table, $options, $fields)
	{
		$set = '';
		$x = 1;

		$editing = $options[0];
		$val = $options[1];

		foreach($fields as $name => $value)
		{
			$set .= "{$name} = ?";
			if($x < count($fields))
			{
				set .= ", ";
			}
			$x++;
		}

		$sql = "UPDATE {$table} SET {$set} WHERE {$editing} = {$val}";

		if(!$this->query($sql, $fields)->error())
		{
			return true;
		}

		return false;
	}

	public function delete($table, $where)
	{
		return $this->action("DELETE ", $table, $where);
	}

	public function get()
	{
		return $this->action("SELECT *", $table, $where);
	}

	public function error()
	{
		return $this->_error;
	}

	public function count()
	{
		return $this->_count;
	}

	public function results($index = '')
	{
		if(is_numeric($index))
		{
			return $this->single_result($index);
		}
		else
		{
			return $this->_results;
		}
	}

	private function single_result($index)
	{
		return $this->_results[$index];
	}

	public function first()
	{
		return $this->_results[0];
	}
}
And here are a few examples of use:

Code: Select all

<?php

require_once 'DB.php';

$db = DB::getInstance();

// Read
$get = $db->get('mytable', array('username', '=', 'bob1'));

// returns PDO object of Bob.
if($get->count() > 0)
{
	$bob = $get->first();
	echo "Hello, " . $bob->first_name;
}

// Create
$ins = $db->insert('mytable', array(
	'username' => 'bob1',
	'password' => 'iambob',
	'first_name' => 'Bob',
	'last_name' => 'Bobbykins'
));

// Update
$up = $db->update('mytable', array('username', 'bob1'), array(
	'password' => 'iambobnewpassword'
));

// Destroy
$del = $db->delete('mytable', array('username', '=', 'bob1'));
As stated before, this is a very simple wrapper, and I don't really recommend using it in a production site, but is a good start for learning about it.
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

thank you so much for everybody, i will learn PDO now.
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: display data of database

Post by Pazuzu156 »

Also, here is a really good tut series on pdo. I recommend watching all of them.

http://www.youtube.com/playlist?list=PL ... KtWVjA46HC
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

I could displayed my data now, in using your PDO scripte,
but I d'ont know how to make the spacing between each column,
can somebody help me ?

echo "\t\t\t<tr>";
echo "<th>$id</th><td>$name</td><td>$comments <br/> </td>";
echo "</tr>\n";
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: display data of database

Post by Celauran »

Spacing between columns? Use CSS to add padding to your table's cells.
terrenuit
Forum Commoner
Posts: 53
Joined: Tue Jul 08, 2014 2:18 pm

Re: display data of database

Post by terrenuit »

OK! I see
Post Reply