Publishing MySQL data into PHP Columns

Question

im working on a project for basically my friends and i to use. Maybe to use it for other games as well. SpeedRunning! i have made and was able to POST the Data into MySQL with THIS information

<?php include_once('include/action_page.php');?>
<!DOCTYPE HTML>
<meta charset="UTF-8">
<html>
<head>
<title>Roleplayer's Tavern Home</title>
<link href="/style/style.css" rel="stylesheet" type="text/css">
<script src="/include/jquery-1.11.2.min.js"></script>
<?php include_once('/include/rpt_site_no_script.php');?>

</head>
<body onload="">
<div class="page_container" name="page_container">
    <div id="page_header">
        <!-- The title of the webpage -->
        <div style="max-width:250px; overflow:hidden">
          <span id="header_title"><img src="style/logo.png"     style="width:225px; height:67px;"/></span>
          </div>

    </div>
    <!-- Left side bar -->
    <div id="page_container_left">
        <h3 id="page_content_header">Submit your data!</h3>

<?php include('include/submit_data.php');?>

    </div>
    <div id="page_container_right"
        style="overflow-y: auto; max-height: 100%">
        <h3 id="page_content_header">Donations for website?</h3>
        <?php 

        ?>
    </div>
    <!-- Main Content -->
    <div id="page_content_container_main_page">
        <div class="page_content_container">
            <h2 id="page_content_container_header">Leaderboard WOO WOO</h2>
            <hr>


            <p id="page_content_container_content">


<h2>Players That Have Beaten Mad Pack 2</h2>

    <?php include('include/leaderboard.php');?>
        </div>
    </div>
    <br>
    <!-- Footer -->
<div id="page_footer">
<ol id="footer_list">
    <li>Copyright &copy; Roleplayer's Tavern 2015-2016 - All Rights     Reserved</li>
    <li style="font-size:12px">Your IP address <?php echo         $_SERVER['REMOTE_ADDR']; ?> will be logged for security reasons.</li>
</ol>
</div>


</div>
<script type="text/javascript">
var element=document.getElementsByName('page_container')[0];
var applyTo=document.getElementById('page_container_left');
var applyTo2=document.getElementById('page_container_right');
applyTo.style.height = (element.offsetHeight - 2) + "px";
applyTo2.style.height = (element.offsetHeight - 2) + "px";

window.onresize = function(event) {
    var element=document.getElementsByName('page_container')[0];
    var applyTo=document.getElementById('page_container_left');
    var applyTo2=document.getElementById('page_container_right');
    applyTo.style.height = (element.offsetHeight - 2) + "px";
    applyTo2.style.height = (element.offsetHeight - 2) + "px";
}
</script>

</body>
</html>

as this is the HTML format.

Action_page.php is the page for submitting the information

<?php 
$servername = "localhost";
$username = "USERNAME";
$password = "PASSWORD";
$mysqlDatabaseName = "SpeedRun";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $mysqlDatabaseName);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(-1);

$sql = "INSERT INTO MineCraftRecords (MineCraftName, LevelSeed, Day, Time)
VALUES ('$_POST[MinecraftName]', '$_POST[LevelSeed]', '$_POST[Day]',     '$_POST[Time]')";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
echo "Turtle Mode Activated";
$conn->close();
?>
<meta http-equiv="Location" content="https://rptavern.org/SpeedRun/">

and im having trouble on getting the page to LOAD the information provided into the leaderboard.php as this is what i have so far.

<?php
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(-1);

$servername = "localhost";
$username = "USERNAME";
$password = "PASSWORD";
$mysqlDatabaseName = "SpeedRun";

$query="SELECT * FROM MineCraftRecords";$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;while ($i < $num) {CODE$i++;}
$variable=mysql_result($result,$i,"fieldname");
$field1-name=mysql_result($result,$i,"MineCraftName");
$field2-name=mysql_result($result,$i,"LevelSeed");
$field3-name=mysql_result($result,$i,"Day");
$field4-name=mysql_result($result,$i,"Time");
$field5-name=mysql_result($result,$i,"id");


// Create connection
$conn = mysqli_connect($servername, $username, $password,     $mysqlDatabaseName);  
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}   
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}  
echo "Turtle Mode Activated";
?>

Im basically trying to base it off this website http://www.speedrun.com/mc but not as technical. Just to show the SpeedRunning time and have the TIME the top of the list.

Any help is awesome, as im very new to creating stuff like this. i will take the time to read everyone's comments and suggestions that you all can provide :D

why are you using `mysqli_` in one area, but then switching to `mysql_`? those different functions do NOT intermix. — Funk Forty Niner, Jun 16 '15 at 00:10
https://php.net/mysqlinfo.api.choosing that translates to "use the same API, from connection to query". — Funk Forty Niner, Jun 16 '15 at 00:14
well i have said that im very new to this. so error's is what i expect from myself. but a person to learn is to create :) — Skippy The Canadian Kangaroo, Jun 16 '15 at 00:14
where it has the // Create Connection it's just a way to tell me that the database has been connected and that part isn't broken. @Fred-ii- — Skippy The Canadian Kangaroo, Jun 16 '15 at 00:23
@Fred-ii- it is connecting. but i can't get it to display the information into Columns, you can see it here https://rptavern.org/SpeedRun/index.php — Skippy The Canadian Kangaroo, Jun 16 '15 at 00:31
You need a connection to the database_before_ running any query through it. If you use mysqli_connect() to establish that connection stick to the functions that start with mysqli_ , not mysql_ — piotrm, Jun 16 '15 at 00:31
@piotrm if you wish to pass me an example. it would be greatly appreciated cause im not familure with this coding yet. only the basic little things. only spent about 4 hours of reading and coding i have done to get this far — Skippy The Canadian Kangaroo, Jun 16 '15 at 00:49
@Fred-ii- this is NOT a question of putting MySQLi and MySQL together, this is a COMPLETE different subject. that was just an accident i didn't notice. — Skippy The Canadian Kangaroo, Jun 16 '15 at 01:40

score 1 · Answer 1 · answered Jun 16 '15 at 00:44

1

I suggest cleaning up your code. Some thing that can be done is that you can separate the dB connect details into a separate file. This will optimize your code and will it make sure to change the details later on as your project grows.

answered Jun 16 '15 at 00:44

Arjun Dhiman

164
8

score 0 · Answer 2 · edited Nov 11 '15 at 08:44

If you would ever consider using PDO here is a code example that will help you out. I'm not sure if mySQLi is similar to PDO but have a look anyway. It's super easy to use and implement in as many pages as possible!

HTML/PHP (displaying data in table):

<script language="JavaScript" type="text/javascript">
function checkDelete(){
    return confirm('Are you sure?');
}
</script>
</head>

<body>
    <?php
    ini_set("display_errors", 1);
    ini_set("display_startup_errors", 1);
    error_reporting(-1);
    require_once("../DAL/db_functions.php");

    //Run query on branch table
    readQuery("M_Branch");


//If there are any details in branch table continue

    if($numRecords === 0){
        echo "<p>No Branches Found!</p>";
    }   
    else{
        $arrRows = NULL;

        //Create table and headings
        echo "<table id='mavis' border='1' width='100%'>";
        echo "<tr>";
        echo "<th>Branch Code</th>";
        echo "<th>Branch Name</th>";
        echo "<th>Manager</th>";
        echo "<th>Branch Address</th>";
        echo "<th>Suburb</th>";
        echo "<th>State</th>";
        echo "<th>Post Code</th>";
        echo "<th>Phone Number</th>";
        echo "<th>Fax Number</th>";
        echo "<th></th>";
        echo "</tr>";

        while($arrRows = $stmt->fetch(PDO::FETCH_ASSOC)){
            echo "<tr>";
            echo "<td>".$arrRows['Branch_Code']."</td>";
            echo "<td>".$arrRows['Branch_name']."</td>";
            echo "<td>".$arrRows['Manager']."</td>";
            echo "<td>".$arrRows['Branch_Address']."</td>";
            echo "<td>".$arrRows['Suburb']."</td>";
            echo "<td>".$arrRows['State']."</td>";
            echo "<td>".$arrRows['Post_code']."</td>";
            echo "<td>".$arrRows['Phone']."</td>";
            echo "<td>".$arrRows['Fax']."</td>";

//Cannot delete already created records - Foreign key constraint fails
//If phpMyadmin were to delete one then other tables will incur problems            
            echo "<td><a href='edit_branch.php?ID=$arrRows[Branch_Code]'>Edit</a>";

            echo "<br /><a href='../BLL/delete_confirm.php?TYPE=Branch&amp;ID=$arrRows[Branch_Code]' onClick='return checkDelete()'>Delete</a></td></tr>";

        }
        echo "</table>";
        echo "<form action='../DAL/add_branch.php' method='post'>";
        echo "<input type='submit' value='Add a New Branch' />";
        echo "</form>";

        echo "<p></P><P>$numRecords Records Returned</P>";
    }
?>
</body>

Here is my connect and readQuery function located in ../Db_functions.php

I have created functions that are reusable and can be used with multiple DB tables.

//Database connection Variables
$localhost = "localhost";
$user = "root";
$password = "root";
$db = "Mavis";
$dsn = "mysql:host=$localhost;dbname=$db";

//Declare Global Variables
$dbConnection = NULL;
$stmt = NULL;
$numRecords = NULL;



//This connect database function can be used to connect anywhere
function connect(){

    //These are variables from the other file (dblibary) - global allows access to these variables
    global $user, $password, $dsn, $dbConnection; //Required to access the global variables.

    try{
        $dbConnection = new PDO($dsn, $user, $password); 
        $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $error){
        //display error message if connection doesnt work
         echo "The following error occured: " . $error->getMessage();

    }

}

Read Query:

function readQuery($table){
        global $numRecords, $dbConnection, $stmt;
        connect();

        $sqlStr = "SELECT * FROM " . $table.";";

        try{
            $stmt = $dbConnection->query($sqlStr);

            if($stmt === false){
                die("Error executing the qquery: $sqlStr");   
            }
        }
        catch(PDOException $error){
            echo "An Error occured: " . $error->getMessage();   
        }
        $numRecords = $stmt->rowCount();

        //Close the DB connection
        $dbConnection = NULL;
    }

ill be trying this out. i THINK i mainly get it. ill give it a shot. if i can't get it to work ill let you know. and thanks @James111 — Skippy The Canadian Kangaroo, Jun 16 '15 at 02:37
awesome! It's really easy to use PDO. I'd be happy to give you some more examples...such as inserting or updating! @SkippyTheCanadianKangaroo — James111, Jun 16 '15 at 05:54

Publishing MySQL data into PHP Columns

2 Answers2