php read data from table

garethaw · February 6, 2015, 7:09pm

Hello,

I want to create dyanmic webpage using php so that when data is inserted into my mysql database it will create a new html article within my php page with the data inside.

This is what I want it too look like http://mywebsite.nn.pe/free/test.php

basically I’m asking for any guidance you have to offer whether it be what I should look into (google) to achieve the result I’m looking for or hard code

Thanks.

JimL · February 6, 2015, 7:18pm

set up a mysqli or pdo connection
do a query to fetch all users/accounts
loop over them and display the data

garethaw · February 6, 2015, 8:32pm

[php]<?php
//conection:
$link = mysqli_connect(“localhost”,"","","") or die("Error " . mysqli_error($link));

//consultation:

$query = “SELECT * FROM list” or die(“Error in the consult…” . mysqli_error($link));

//execute the query.

$result = $link->query($query);

//display information:

while($row = mysqli_fetch_array($result)) {
echo $row[“username”] . “
”;
echo $row[“password”] . “
”;
echo $row[“other”] . “
”;
}
?> [/php]

This is what I’ve got so far I now want to put the data inside this and then have it so that when new data is input a new article will be created.

[code]

Username:: data from sql here
Password:: data from sql here
Other:: data from sql here

[/code]

JimL · February 6, 2015, 8:48pm

You are mixing object oriented and procedural mysqli code (mysql_connect and mysqli_fetch_array are procedural, $link-> is object oriented), you need to choose either.

Note that it’s generally not recommended to just die.

[php]<?php
$link = mysqli_connect(“localhost”,"","","") or die("Error " . mysqli_error($link));
$result = mysqli_query($link, “SELECT * FROM list”) or die(“Error in the consult…” . mysqli_error($link));

$accounts = array();
while($row = mysqli_fetch_array($result)) {
$accounts[] = $row;
}
?>

// view, could be moved to another file, ie views/accountslist.php

<?php foreach ($accounts as $account) { ?>

Username:: <?= $account["username"] ?>
Password:: <?= $account["password"] ?>
Other:: <?= $account["other"] ?>

/article> <?php }[/php]

garethaw · February 6, 2015, 9:01pm

Thanks a bunch yeah didn’t realise I can’t mix the two I managed to get yours working http://mywebsite.nn.pe/free/guyhelp.php thanks a bunch! As one final thing I expect to have alot of data in the db so I was wondering how I would go around putting in page numbers for example so that the page doesn’t scroll down forever.

JimL · February 6, 2015, 9:29pm

Look into pagination.

It can actually seem pretty complex, but break it up into chunks and it should be possible to wrap your head around with some fiddling.

You need to:

1
Set a page size

2
Add a limit to your query

3
Since you now only fetch a subset of the table, you need to figure out the total number of items. You can…

a
Add SQL_CALC_FOUND_ROWS to the query and fetch the total rows

b
Run a separate query “SELECT count(*) FROM table” to get the row count, this is usually faster

[hr]

Something like this should get you started

[php]<?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query

$link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));

$result = mysqli_query($link, "SELECT count(*) FROM list LIMIT $page,$pageSize") or die("Error in the consult.." . mysqli_error($link));
$row = mysqli_fetch_array($result);
$accountsCount = $row[0];

$totalPages = $accountsCount / $pageSize;

$result = mysqli_query($link, "SELECT * FROM list") or die("Error in the consult.." . mysqli_error($link)); 

$accounts = array();
while($row = mysqli_fetch_array($result)) {
  $accounts[] = $row; 
} [/php]

[hr]

With the above you should be able to use ?page=x to change page. Usually you would want a pagination menu after the entries.

[php]

<?= $i ?>

[/php]

If we have 50 accounts and a pageSize of 5 we will get 10 links printed. Links should be styled appropriatly.

garethaw · February 6, 2015, 10:05pm

http://mywebsite.nn.pe/list.php not sure what I’m doing wrong

[php] <?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query

  $link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));

  $result = mysqli_query($link, "SELECT count(*) FROM list LIMIT $page,$pageSize") or die("Error in the consult.." . mysqli_error($link));
  $row = mysqli_fetch_array($result);
 $accountsCount = $row[0];

 $totalPages = $accountsCount / $pageSize;

 $result = mysqli_query($link, "SELECT * FROM list") or die("Error in the consult.." . mysqli_error($link)); 
 
 $accounts = array();
 while($row = mysqli_fetch_array($result)) {
   $accounts[] = $row; 
 }
?>
 
 <ul>

<?php for ($i = 1; $i <= $totalPages; $i++) { ?>

<?= $i ?>

<?php } ?> <?php foreach ($accounts as $account) { ?>

Username:: <?= $account["username"] ?>
Password:: <?= $account["password"] ?>
Other:: <?= $account["other"] ?>

<?php }[/php]

JimL · February 6, 2015, 10:14pm

Ah, I messed up. I added the limit to the wrong query

[php]<?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query

    $link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));

    $result = mysqli_query($link, "SELECT count(*) FROM list") or die("Error in the consult.." . mysqli_error($link));
    $row = mysqli_fetch_array($result);
  $accountsCount = $row[0];

  $totalPages = $accountsCount / $pageSize;

  $result = mysqli_query($link, "SELECT * FROM list LIMIT $page,$pageSize") or die("Error in the consult.." . mysqli_error($link)); 
  
  $accounts = array();
  while($row = mysqli_fetch_array($result)) {
    $accounts[] = $row; 
  }
 ?>[/php]

JimL · February 6, 2015, 10:27pm

Just noticed another problem. We weren’t offsetting the results properly, so instead of getting “from page x”, we got “from item x”.

[php]<?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query

      $link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));
  
      $result = mysqli_query($link, "SELECT count(*) FROM list") or die("Error in the consult.." . mysqli_error($link));
      $row = mysqli_fetch_array($result);
   $accountsCount = $row[0];

   $totalPages = $accountsCount / $pageSize;

   $result = mysqli_query($link, "SELECT * FROM list LIMIT $page*$pageSize,$pageSize") or die("Error in the consult.." . mysqli_error($link)); 
   
   $accounts = array();
   while($row = mysqli_fetch_array($result)) {
     $accounts[] = $row; 
   }
  ?>[/php]

garethaw · February 6, 2015, 10:31pm

JimL:

Just noticed another problem. We weren’t offsetting the results properly, so instead of getting “from page x”, we got “from item x”.

[php]<?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query
      $link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));
  
      $result = mysqli_query($link, "SELECT count(*) FROM list") or die("Error in the consult.." . mysqli_error($link));
      $row = mysqli_fetch_array($result);
   $accountsCount = $row[0];

   $totalPages = $accountsCount / $pageSize;

   $result = mysqli_query($link, "SELECT * FROM list LIMIT $page*$pageSize,$pageSize") or die("Error in the consult.." . mysqli_error($link)); 
   
   $accounts = array();
   while($row = mysqli_fetch_array($result)) {
     $accounts[] = $row; 
   }
  ?>[/php]

I made the modification but getting an error now http://mywebsite.nn.pe/list.php

JimL · February 6, 2015, 10:33pm

Seems like we have established that not testing code can lead to problems

[php] <?php
$pageSize = 5;
$page = isset($_GET[‘page’]) && (int) $_GET[‘page’] > 1 ? (int) $_GET[‘page’]-1 : 0;
// above we set the current page to a default (1) if get page is not set. If it is set we make sure it’s an valid integer. Note that as usual we start our index at 0 instead of 1. This just makes it easier when we add the limit to our query

        $link = mysqli_connect("localhost","","","") or die("Error " . mysqli_error($link));
    
        $result = mysqli_query($link, "SELECT count(*) FROM list") or die("Error in the consult.." . mysqli_error($link));
        $row = mysqli_fetch_array($result);
    $accountsCount = $row[0];

    $totalPages = $accountsCount / $pageSize;
    $offset = $page * $pageSize;

    $result = mysqli_query($link, "SELECT * FROM list LIMIT $offset,$pageSize") or die("Error in the consult.." . mysqli_error($link)); 
    
    $accounts = array();
    while($row = mysqli_fetch_array($result)) {
      $accounts[] = $row; 
    }
   ?>[/php]

garethaw · February 6, 2015, 10:44pm

seems to be working now anyways much appreicated!, I just need to go back over your original instructions and figure out how to add more pages now oh and I suppose there is one more thing might be pushing my luck but could you make it so that page numbers aren’t ordered in bullet pointed list but in a more conventional format so there side by side with space in between if possible? i.e

1 2 3 4 5 6 7 etc…

JimL · February 6, 2015, 10:49pm

Just make a form that posts to a page (itself?) that stores the form data in the list table.

You can change the look of it with styling. add this to your css file or in style tags

[code]ul {
list-style: none;
}

li {
display: inline;
}

li a {
text-decoration: none;
color: #333;
padding: .2em;
margin: .1em;
}[/code]

garethaw · February 6, 2015, 10:56pm

Alright yeah my bad I probably could done that myself didn’t realise it was matter of just changing the css thanks though!