Display all data mysql php

How to display all the data of a table stored in a MySQL database in PHP

I am trying to display the data of a table containing an image in blob format, but I can only get the last inserted record to be displayed. I would like to show all the records with their respective images, and when adding a new record it will be shown with the rest of the table data. MYSQL noticias: idNoticia(PK), idUser(FK), titulo, imagen(LONGBLOB), texto, fecha PHP — insertarNoticias.php

include('./DB.php'); session_start(); $idUser = $_SESSION['usuario'][0]; $titulo = $_POST['titulo-noticia']; $texto = $_POST['texto-noticia']; $fecha = $_POST['fecha-noticia']; $nombreImagen = $_FILES['imagen']['name']; $carpetaDestino = $_SERVER['DOCUMENT_ROOT'] . '/masterD/trabajo_final_php/img/'; move_uploaded_file($_FILES['imagen']['tmp_name'], $carpetaDestino . $nombreImagen); $conexion = DB::conn(); $imagen = fopen($carpetaDestino . $nombreImagen, "r"); $archivoBytes = fread($imagen, intval(filesize($carpetaDestino . $nombreImagen))); fclose($imagen); $sentencia = 'INSERT INTO noticias (idUser, titulo, imagen, texto, fecha) VALUES (:idUser, :titulo, :imagen, :texto, :fecha)'; $consulta = $conexion->prepare($sentencia); $consulta->bindParam(':idUser', $idUser); $consulta->bindParam(':titulo', $titulo); $consulta->bindParam(':imagen', $archivoBytes); $consulta->bindParam(':texto', $texto); $consulta->bindParam(':fecha', $fecha); $consulta->execute(); $consulta->closeCursor(); $conexion = null; 

include('./DB.php'); session_start(); $titulo = ''; $imagen = ''; $texto = ''; $fecha = ''; $conexion = DB::conn(); $sentencia = 'SELECT * FROM noticias'; $consulta = $conexion->prepare($sentencia); $consulta->execute(); while ($row = $consulta->fetch(PDO::FETCH_ASSOC)) < $titulo = $row['titulo']; $imagen = $row['imagen']; $texto = $row['texto']; $fecha = $row['fecha']; >$consulta->closeCursor(); $conexion = null; 

  
 
   Crear noticia  
 
 

Источник

How get all values in a column using PHP?

I’ve been searching for this everywhere, but still can’t find a solution: How do I get all the values from a mySQL column and store them in an array? For eg: Table Name: Customers Column names: ID, Name # of rows: 5 I want to get an array of all the 5 names in this table. How do I go about doing that? I am using PHP, and I was trying to just:

SELECT names FROM Customers 

Notice: most answers still use the old MySQL functions, these functions where deprecated in PHP 5, and removed in PHP 7, I suggest you change the accepted answer to one that works in the latest PHP

7 Answers 7

Here is a simple way to do this using either PDO or mysqli

$stmt = $pdo->prepare("SELECT Column FROM foo"); // careful, without a LIMIT this can take long if your table is huge $stmt->execute(); $array = $stmt->fetchAll(PDO::FETCH_COLUMN); print_r($array); 

$stmt = $mysqli->prepare("SELECT Column FROM foo"); $stmt->execute(); $array = []; foreach ($stmt->get_result() as $row) < $array[] = $row['column']; >print_r($array); 

Array ( [0] => 7960 [1] => 7972 [2] => 8028 [3] => 8082 [4] => 8233 ) 

Note that this answer is outdated! The mysql extension is no longer available out of the box as of PHP7. If you want to use the old mysql functions in PHP7, you will have to compile ext/mysql from PECL. See the other answers for more current solutions.

$result = mysql_query("SELECT names FROM Customers"); $storeArray = Array(); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) < $storeArray[] = $row['names']; >// now $storeArray will have all the names. 

Hey! This answer to this popular question breaks in PHP 7 because mysql_* is no longer supported. Could you edit this so that it uses the more modern APIs (PDO or MySQLi)? Alternatively, would you object other people to edit this information in?

I would use a mysqli connection to connect to the database. Here is an example:

$connection = new mysqli("127.0.0.1", "username", "password", "database_name", 3306); 

The next step is to select the information. In your case I would do:

$query = $connection->query("SELECT `names` FROM `Customers`;"); 

And finally we make an array from all these names by typing:

$array = Array(); while($result = $query->fetch_assoc()) < $array[] = $result['names']; >print_r($array); 

So what I’ve done in this code: I selected all names from the table using a mysql query. Next I use a while loop to check if the $query has a next value. If so the while loop continues and adds that value to the array ‘$array’. Else the loop stops. And finally I print the array using the ‘print_r’ method so you can see it all works. I hope this was helpful.

Since mysql_* are deprecated, so here is the solution using mysqli.

$mysqli = new mysqli('host', 'username', 'password', 'database'); if($mysqli->connect_errno>0) < die("Connection to MySQL-server failed!"); >$resultArr = array();//to store results //to execute query $executingFetchQuery = $mysqli->query("SELECT `name` FROM customers WHERE 1"); if($executingFetchQuery) < while($arr = $executingFetchQuery->fetch_assoc()) < $resultArr[] = $arr['name'];//storing values into an array >> print_r($resultArr);//print the rows returned by query, containing specified columns 

There is another way to do this using PDO

 $db = new PDO('mysql:host=host_name;dbname=db_name', 'username', 'password'); //to establish a connection //to fetch records $fetchD = $db->prepare("SELECT `name` FROM customers WHERE 1"); $fetchD->execute();//executing the query $resultArr = array();//to store results while($row = $fetchD->fetch()) < $resultArr[] = $row['name']; >print_r($resultArr); 

Источник

Can I display all tables and data in a MySQL database?

I want to output all tables in a MySQL database, along with all the records in each of the tables, as a single web page. I can do this for one table using the SELECT function, but want to do this for a rapidly changing database. New tables will be added and deleted over time, so I want a generic function which will display all data from all tables in a database. Is this possible?

you can query the information schema tabels to get all of this dev.mysql.com/doc/refman/5.0/en/information-schema.html

4 Answers 4

This will list all tables in a database and order them by their table name and ordinal position. I have omitted some of the columns that you may not need, but double check the column selections.

Ok. you are getting more than I would normal give away, but try this and see if it is what you are looking for.

SchemaTableColumnPositionKeyTypeMax Length"; $table_change_row = " TableColumnPositionKeyTypeMax Length"; $table_bottom = ""; $current_schema = ''; $current_table = ''; echo $table_top; while ($row = mysql_fetch_object($result)) < if ($current_schema!=$row->TABLE_SCHEMA && !empty($current_schema)) < echo $table_bottom; echo $table_top; >if ($current_table!=$row->TABLE_NAME) < echo $table_change_row; >$current_schema = $row->TABLE_SCHEMA; $current_table = $row->TABLE_NAME; echo " $row->TABLE_SCHEMA $row->TABLE_NAME $row->COLUMN_NAME $row->ORDINAL_POSITION $row->COLUMN_KEY $row->DATA_TYPE $row->CHARACTER_MAXIMUM_LENGTH "; > echo $table_bottom; ?> 

Источник

Displaying all tables in a database

How do I display out all the information in a database (All tables and records) using PHP? I read displaying tables as HTML table but how do I do it for all the tables? I tried the example here: http://davidwalsh.name/html-mysql-php But it shows the table names, how do I also display all the values?

3 Answers 3

query("SHOW TABLES"); while ($row = $result->fetch_row()) < $table = $row[0]; echo '
', $table, '
'; $result1 = $mysqli->query("SELECT * FROM `$table`"); echo 'query("SHOW COLUMNS FROM `$table`"); echo ''; while ($row3 = $column->fetch_row()) < echo ''; > echo ''; while ($row2 = $result1->fetch_row()) < echo ''; foreach ($row2 as $key => $value) < echo ''; > echo ''; > echo '
' . $row3[0] . '
', $value, '

'; > $mysqli->close(); 

it could be that aandroidtest did not enable the following inside php.ini: extension=php_mysqli.dll and i also recommend enabling the pdo extension

you can use the recursive function to display the tree Structure of your database.

If you want to pull all tables with the values in mysql , you can use the following code:

 echo $table[0]; $query = "SELECT * FROM ".$table[0]; //prints out tables name $result = mysql_query($query); PullValues($result); //call function to get all values > //function to pull all values from tables function PullValues($result) < if($result == 0) < echo "Error ".mysql_errno().": ".mysql_error().""; > elseif (@mysql_num_rows($result) == 0) < echo("Query completed. No results returned.
"); > else < echo ""; for($i = 0;$i < mysql_num_fields($result);$i++) < echo ""; > echo ""; for ($i = 0; $i < mysql_num_rows($result); $i++) < echo ""; $row = mysql_fetch_row($result); for($j = 0;$j < mysql_num_fields($result);$j++) < echo(""); > echo ""; > echo "
 
[Num]
" . $i . " - " . mysql_field_name($result, $i) . "
 
 
[$i]
" . $row[$j] . "
 
"; > //end else > ?> 

I am using this and works fine in mysql , for mysqli you need to tweak it a very little.

Источник