Using MySQLI how can I display data from my database on my website


Problem :

I have long procrastinated switching to MySQLI from MySQL. I have started a new project and decided I'd rather go ahead and start it off with good habits instead of bad ones with deprecated MySQL.

I am trying to figure out how to iterate through a table in my database and display that data on my website. The process, I am sure, is straight forward but the explanation so you will understand the question is less so. I will try to be brief.

Example: I have a database named DATABASE that contains a table called TABLE. On my website I have 5 categories. Each category has a list of 5 items each. Each item in each list in turn has 5 values assigned to it. IE: name, dob, year, month, date. Those 5 values I have stored in my database for each list item in each category.

Visual Representation: http://imgur.com/PMmbOV6 (Each one of the list items has 5 values )

Code: [COLUMN_#] represents the column in the db table corresponding to the value I'd like to pull and insert into the page/html

<div class="category_1"> <!-- First loop iteration -->
    <div class="item_1"> 
        <a title="[COLUMN_1]" href="[COLUMN_2]">[COLUMN_3]</a>
        <span class="class">[COLUMN_4]</span>
        <span class="class"> [COLUMN_5]</span>
    </div>
</div>

<div class="category_1"> <!-- Second loop iteration -->
    <div class="item_2"> 
        <a title="[COLUMN_1]" href="[COLUMN_2]">[COLUMN_3]</a>
        <span class="class">[COLUMN_4]</span>
        <span class="class"> [COLUMN_5]</span>
    </div>
</div>

<div class="category_1"> <!-- Third loop iteration -->
    <div class="item_3"> 
        <a title="[COLUMN_1]" href="[COLUMN_2]">[COLUMN_3]</a>
        <span class="class">[COLUMN_4]</span>
        <span class="class"> [COLUMN_5]</span>
    </div>
</div>

And so on to the 5th loop iteration for category 1. (one iteration for each list item.)

All the values for all list items from all 5 categories are in the same database so I assume if I wanted to pull the values into the page from the db for category 1 the code would need to be something like - select all from table where category is equal to 1 - I write a loop. Then when the loop finds no more list items with category equal to 1 it ends and moves on to the next script below that to query list items for category 2. And so on.

PHP Code so far:

<?php
// Connect to and select a database
$db = new mysqli('localhost', 'root', '', 'DATABASE');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

// Query the table 'TABLES' (I'm also assuming I only need to query just the table from here)
$sql = <<<SQL
    SELECT *
    FROM `TABLE`
SQL;

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
?>

Thanks in advance this problem is really getting me and I don't want to go back to MySQL. Everyone here tells me not to! =)



Solution :

The big thing to remember in mysqli OOP is that when you get your $result, it too is an object. So you would perform operations with that instead

while($row = $result->fetch_assoc()) {
    echo $row['fieldname'];
}

Mysql Tutorials

Mysql Howto..

how to show datagridview look like it's downloading data from mysql

How to select same table in update statement with MySQL

How to check for blocks of availability in a MySQL booking database?

How to get the next auto-increment id in mysql

How to check in batch script if “mysqldump” and “mysql” commands succeeded or not?

How to Prevent Concurrent User Logins in PHP/MySQL Site?

Nested query is so slow in MySQL. How to optimize it?

OOP Challenge How to nest using foreach in php mysql result set?

How do I update a table to add a primary key and update all of the existing rows with incremented IDs?

How to run two queries?(Joomla, mysql)

How to make execution update reverse query mysql by id?

jQuery sortables and how to save the current sorting to mysql

How to Convert from .Net TimeZone to MySQL TimeZone

sql: how to select by two array

How to name wildcard select columns in MySQL?

How to display data twice from a mysql table in php

How to remove the first word in an query

How to make query that ignores undefined variables?

How to populate an android spinner with data coming from mysql

mysql query - one query to create table showing sales by month

Postgresql vs. MySQL: how do their data sizes compare to each other?

how to accelerate c++ for loop code into query using MySQL

How to optimize mysql query with multiple fulltext matches

How to create UNIQUE key in mysql with BLANK value?

How do I fix django.db.utils.IntegrityError: duplicate key value violates unique constraint?

How can I use mysql null safe equality operator <=> in HQL?

How to count total by current year of current month in mysql

How to filter for a specific set of characters ending with any digit?

How to check if a value exists in tableA before inserting in tableB?

How to transform a file into INSERT with foreign keys?