Php noob help

E

eternaloptimist

Well-Known Member
Joined
Jul 10, 2013
Messages
175
Hey all.
I need a bit of help. I have a table with many items, when the id cell is clicked it I'd like to display the additional information about that row in a modal.
I need to send the clicked cell id//number so I can query the database, how can I achieve this?
I'm a total noob and would appreciate any help I get.
Thanks in advance!

 
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
If your willing to wait till 8:30 then I'll write you a script that can do this. I like these sorts of things.
 
Last edited:
G

Genisys

Honorary Master
Joined
Jan 12, 2016
Messages
11,217
eternaloptimist said:
Hey all.
I need a bit of help. I have a table with many items, when the id cell is clicked it I'd like to display the additional information about that row in a modal.
I need to send the clicked cell id//number so I can query the database, how can I achieve this?
I'm a total noob and would appreciate any help I get.
Thanks in advance!

Click to expand...
With or without a Framework? If you are using a framework, which framework are you using?
 
Necropolis

Necropolis

Executive Member
Joined
Feb 26, 2007
Messages
8,401
Are you using a database of some description?

Or is you data hard coded into the PHP?
 
G

Genisys

Honorary Master
Joined
Jan 12, 2016
Messages
11,217
What database are you using? Is it MSSQL, My SQL, SQLITE, Oracle SQL or something in that direction?
 
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
Right, what you want to do essentially is somehow pass the id to a handler and have that handler return additional info of the ID from the database, this is how I would do it.

(I left out pagination and search/filter delete and update cause I only had 15 minutes)

I assume you are using MySQL, if you are not then you should ;)

database.php

PHP: 
<?php
class Database
{
    private static $dbName = 'your DB name' ;
    private static $dbHost = 'localhost' ;
    private static $dbUsername = 'user';
    private static $dbUserPassword = 'password';
     
    private static $cont  = null;
     
    public function __construct() {
        die('Init function is not allowed');
    }
     
    public static function connect()
    {
       // One connection through whole application
       if ( null == self::$cont )
       {     
        try
        {
          self::$cont =  new PDO( "mysql:host=".self::$dbHost.";"."dbname=".self::$dbName, self::$dbUsername, self::$dbUserPassword); 
        }
        catch(PDOException $e)
        {
          die($e->getMessage()); 
        }
       }
       return self::$cont;
    }
     
    public static function disconnect()
    {
        self::$cont = null;
    }
}
?>

list_view.php
PHP: 
<!DOCTYPE html>
<?php $thisPage="Home Page";
  include('php/database.php');

?>
<html lang="en">
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>


    <title>Country<?php if ($thisPage!="") echo " | $thisPage"; ?></title>
</head>

<body>

    <!--  Navigation  -->
    <?php include("php/menu.php"); ?>

    <!--  Page Content  -->
	<main class="kfc-wrapper>
      <table class="table">
        <thead>
          <tr>
            <th>ID</th>
            <th>City</th>
            <th>Country</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>
        <?php
          $pdo = Database::connect();


          $sql = "SELECT * FROM items";
          $sth = $pdo->prepare($sql);
          $sth->execute();

            foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
                  echo '<tr>';
                  echo '<td>'. htmlspecialchars($row['id']) . '</td>';
                  echo '<td>'. htmlspecialchars($row['city']) . '</td>';
                  echo '<td>'. htmlspecialchars($row['country']) . '</td>';
                  echo '<td width=250>';
                  echo '<a class="button-view" href="detailed_view.php?id='. htmlspecialchars($row['id']) .'">View</a>';
                  echo ' ';
                  echo '<a class="button-update" href="update_item.php?id='. htmlspecialchars($row['id']) .'">Update</a>';
                  echo ' ';
                  echo '<a class="button-delete" href="delete_item.php?id='. htmlspecialchars($row['id']) .'">Delete</a>';
                  echo '</td>';
                  echo '</tr>';
         }
         Database::disconnect();
        ?>
        </tbody>
      </table>
	</main>
	<!-- Footer -->
    <?php include("php/footer.php"); ?>
</body>
</html>

detailed_view.php

PHP: 
<!DOCTYPE html>
<?php $thisPage="Item View";
  include('php/database.php');
    $id = null;
    if ( !empty($_GET['id'])) {
        $id = $_REQUEST['id'];
    }
     
    if ( null==$id ) {
        header("Location: list_view.php");
    } else {
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "SELECT * FROM items where id = ?";
        $q = $pdo->prepare($sql);
        $q->execute(array($id));
        $data = $q->fetch(PDO::FETCH_ASSOC);
        Database::disconnect();
    }
?>
<html lang="en">
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>


    <title>Country<?php if ($thisPage!="") echo " | $thisPage"; ?></title>
</head>

<body>

    <!--  Navigation  -->
    <?php include("php/menu.php"); ?>

    <!--  Page Content  -->
	<main class="kfc-wrapper>
      <table class="table">
        <thead>
          <tr>
            <th>ID</th>
            <th>City</th>
            <th>Country</th>
            <th>Population</th>
            <th>Province</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td><b><?php echo htmlspecialchars($data['id']);?></b></td>
            <td><b><?php echo htmlspecialchars($data['city']);?></b></td>
            <td><b><?php echo htmlspecialchars($data['country']);?></b></td>
            <td><b><?php echo htmlspecialchars($data['population']);?></b></td>
            <td><b><?php echo htmlspecialchars($data['province']);?></b></td>
          </tr>
        </tbody>
      </table>
	</main>
	<!-- Footer -->
    <?php include("php/footer.php"); ?>
</body>
</html>
 
Last edited:
IndigoIdentity

IndigoIdentity

Expert Member
Joined
May 10, 2010
Messages
1,964
eternaloptimist said:
when the id cell is clicked it I'd like to display the additional information about that row in a modal.
Click to expand...

When you click on the cell, you can register this event with jQuery on the front end so on click do x.

In which case, on the click event, you can make an AJAX request to the back end which might spit back some data that is relative to the cell but in JSON format.

You can then use this data to populate the values within the modal.

You can then open the model so that the user can see it.

Here is the docs on jQuery get: https://api.jquery.com/jquery.get/

Thor kindly provided you with some code to query the database and you could make use of this for the end point that you would query with jQuery, however, you would amend it so that you simply output the array of data like echo json_encode($data);
 
Necropolis

Necropolis

Executive Member
Joined
Feb 26, 2007
Messages
8,401
Hi Thor,

Please learn to use stored procedures.

Thanks,

Necropolis
 
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
Necropolis said:
Hi Thor,

Please learn to use stored procedures.

Thanks,

Necropolis
Click to expand...
You talk a lot lately.

The code is there, fix it.

Give some input so the next guy knows what to do.
 
Last edited:
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
Necropolis said:
No need to get pissy.

Just learn to use stored procedures - embedding SQL queries directly in your code is poor programming practice.
Click to expand...
No no no, you've been on my case now for a while.

Fix the code above then I'll update it once your done.
 
Necropolis

Necropolis

Executive Member
Joined
Feb 26, 2007
Messages
8,401
Thor187 said:
No no no, you've been on my case now for a while.

Fix the code above then I'll update it once your done.
Click to expand...

Where did I say that code was wrong / didn't work?

I just suggested that you start using stored procedures.
 
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
Necropolis said:
Where did I say that code was wrong / didn't work?

I just suggested that you start using stored procedures.
Click to expand...

Add the stored procedures and I will update it.

I would like to see how you do a stored procedure with a select statement in the above example. teach me.
 
Last edited:
Thor

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
44,236
Necropolis said:
No.
Click to expand...

That's what I thought.

/moving on.

If this was production and not done in a few minutes of course I would do something like:

PHP: 
DELIMITER $$
 
CREATE PROCEDURE GetItem()
BEGIN
 SELECT *
 FROM items;
    END$$

and then

PHP: 
$sql = 'CALL GetItem()';
 
Last edited:
E

eternaloptimist

Well-Known Member
Joined
Jul 10, 2013
Messages
175
Thanks for all the replies. I am using MySQL and plain PHP(mysqli) and vanilla JavaScript. Many thanks Thor, I'll work on it now! I'll let everyone know how I get on.
 
K

_kabal_

Executive Member
Joined
Oct 24, 2005
Messages
5,922
I am sure stored procedures are useful in some cases and environments, but this is certainly not one of them.


rather work on removing all these leaky abstractions, like calling and using SQL in your views....


because I am being nice, but it will probably be ignored anyway

wont even use "fancy" DI


database.php

PHP: 
<?php

abstract class Database
{
    private static $dbName = 'your DB name' ;
    private static $dbHost = 'localhost' ;
    private static $dbUsername = 'user';
    private static $dbUserPassword = 'password';
     
    private static $cont  = null;
     
    public static function connect()
    {
       // One connection through whole application
       if ( null == self::$cont )
       {     
        try
        {
          self::$cont =  new PDO( "mysql:host=".self::$dbHost.";"."dbname=".self::$dbName, self::$dbUsername, self::$dbUserPassword); 
        }
        catch(PDOException $e)
        {
          die($e->getMessage()); 
        }
       }
       return self::$cont;
    }
     
    public static function disconnect()
    {
        self::$cont = null;
    }
}

ItemService.php
PHP: 
//will eventually need business logic, and then all this SQL related stuff can move into a repository
<?php

require_once(database.php); //can obviously replace this with "autoloading" and "use"

class ItemService {

    private static GET_ITEMS_SQL = "SELECT * FROM items";
    private static GET_ITEMS_SQL = "SELECT * FROM items WHERE id = ?";

    public function getItems { 
         $connection = Database::connect();
         try {
             $statement = $pdo->prepare(GET_ITEMS_SQL );
             $statement ->execute();
          
             //this is just "fancy", but often your DB column names are not what you want to be using in your code (legacy DB's with bad column names
             return array_map($this->itemRowToItem, $statement ->fetchAll(PDO::FETCH_ASSOC));
         } finally {
             Database::disconnect();
         }
    }

    public function getItem($id) { 
         $connection = Database::connect();
         try {
             $statement = $pdo->prepare(GET_ITEM_SQL );
             $statement ->execute();
             $rows = array_map($this->itemRowToItem, $statement ->fetchAll(PDO::FETCH_ASSOC));
             return count($rows) > 0 ? $rows[0] : null;
         } finally {
             Database::disconnect();
         }
    }

    private function itemRowToItem($row) {
        return new Item($row['id'], $row['city'], $row['province'], $row['country], $row['population']);
    }

}

list_view.php
PHP: 
<!DOCTYPE html>
<?php $thisPage="Home Page";
  include('ItemService.php');

?>
<html lang="en">
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>


    <title>Country<?php if ($thisPage!="") echo " | $thisPage"; ?></title>
</head>

<body>

    <!--  Navigation  -->
    <?php include("php/menu.php"); ?>

    <!--  Page Content  -->
	<main class="kfc-wrapper>
      <table class="table">
        <thead>
          <tr>
            <th>ID</th>
            <th>City</th>
            <th>Country</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>
        <?php
          $service = new ItemService();
          $items = $service->getItems();

          foreach ($items as $item) {
                  $id = $item->getId();
                  echo '<tr>';
                  echo '<td>'. $id . '</td>';
                  echo '<td>'. $item->getCity() . '</td>';
                  echo '<td>'. $item->getCountry() . '</td>';
                  echo '<td width=250>';
                  echo '<a class="button-view" href="detailed_view.php?id='.$id.'">View</a>';
                  echo ' ';
                  echo '<a class="button-update" href="update_item.php?id='.$id.'">Update</a>';
                  echo ' ';
                  echo '<a class="button-delete" href="delete_item.php?id='.$id.'">Delete</a>';
                  echo '</td>';
                  echo '</tr>';
         }
        ?>
        </tbody>
      </table>
	</main>
	<!-- Footer -->
    <?php include("php/footer.php"); ?>
</body>
</html>

detailed_view.php

PHP: 
<!DOCTYPE html>
<?php $thisPage="Item View";
  include('ItemService.php');
    $id = null;
    if ( !empty($_GET['id'])) {
        $id = $_REQUEST['id'];
    }
     
    if ( null==$id ) {
        header("Location: list_view.php");
    } else {
        $service = new ItemService();
        $item = $service->getItem($id);
        if ($item == null) {
          //throw 404
        
    }
?>
<html lang="en">
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>


    <title>Country<?php if ($thisPage!="") echo " | $thisPage"; ?></title>
</head>

<body>

    <!--  Navigation  -->
    <?php include("php/menu.php"); ?>

    <!--  Page Content  -->
	<main class="kfc-wrapper>
      <table class="table">
        <thead>
          <tr>
            <th>ID</th>
            <th>City</th>
            <th>Country</th>
            <th>Population</th>
            <th>Province</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td><b><?= $item->getId() ?></b></td>
            <td><b><?= $item->getCity() ?></b></td>
            <td><b><?= $item->getCountry() ?></b></td>
            <td><b><?= $item->getPopulation() ?></b></td>
            <td><b><?= $item->getProvince() ?></b></td>
          </tr>
        </tbody>
      </table>
	</main>
	<!-- Footer -->
    <?php include("php/footer.php"); ?>
</body>
</html>
[/QUOTE]
 
You must log in or register to reply here.
Top