Displaying MYSQL Data into Tables

Ptsface12 · July 17, 2011

Hello,

I currently have a problem with a MySQL script, that'll output data to my page. Here is the script that I have:

This page is called: get_email.php (Just for testing purposes)

<?php
include 'config.php';
?>  
<?php
mysql_connect ("localhost", $username, $password);
@mysql_select_db($database) or die("Unable to select database");
$user_result = mysql_query("SELECT username FROM users");
$email_result = mysql_query("SELECT email FROM users");
print "<table width="100%">";
print "<thead>";
print "<tr>";
print "<th>Username</th>";
print "<th>Email Address</th>";
print "<th>Actions</th>";
print "<th><input name="" type="checkbox" value="" id="checkboxall" /></th>";
print "</tr>";
print "</thead>";
print "<tbody>";
while ($get_email = mysql_fetch_row($user_result)){
while ($get_user = mysql_fetch_row($email_result)){ 
print "<tr>";
foreach ($user_result as $usercred)  
foreach ($email_result as $emailcred)  
print "</td>$user_result</td>";
print "</td>$email_result</td>";
print "</tr>";
}
print "</tbody>";
?>

This page is called: index.php

<? 
include '../header.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PS3Panda • Housekeeping • Home</title>
<link href="styles/layout.css" rel="stylesheet" type="text/css" />
<link href="styles/wysiwyg.css" rel="stylesheet" type="text/css" />
<!-- Theme Start -->
<link href="themes/blue/styles.css" rel="stylesheet" type="text/css" />
<!-- Theme End -->

</head>


<body id="homepage">
<div id="header">
    	<a href="" title=""><img SRC="img/cp_logo.png" alt="Control Panel" class="logo" /></a>
    	<div id="searcharea">
            <p class="left smltxt"><a href="#" title="">Advanced</a></p>
            <input type="text" class="searchbox" value="Search control panel..." onclick="if (this.value =='Search-2520control-2520panel..-2E'){this.value=''}"/>
            <input type="submit" value="Search" class="searchbtn" />
        </div>
    </div>
        
    <!-- Top Breadcrumb Start -->
    <div id="breadcrumb">
    	<ul>	
        	<li><img SRC="img/icons/icon_breadcrumb.png" alt="Location" /></li>
        	<li><strong>Location:</strong></li>
            <li><a href="#" title="">Control Panel</a></li>
            <li>/</li>
            <li class="current">Manage Users</li>
        </ul>
    </div>
    <!-- Top Breadcrumb End -->
     
    <!-- Right Side/Main Content Start -->
    <div id="rightside">
    
    	<!-- Status Bar Start --><!-- Status Bar End -->
        
         <!-- Red Status Bar Start --><!-- Red Status Bar End -->
        
        <!-- Green Status Bar Start --><!-- Green Status Bar End -->
        
        <!-- Blue Status Bar Start --><!-- Blue Status Bar End -->   
    
        <!-- Content Box Start -->
      <div class="contentcontainer">
            <div class="headings alt">
                <h2>Create New User</h2>
            </div>
            <div class="contentbox">
            	<table><form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
                	<tr>
                    	<td width="150"><strong>Current Password:</strong></td>
                        <td width="500"><label for="user">
                          <input name="user" type="text" class="inputbox" id="user" />
                        </label></td>
                    </tr>
                    <tr>
                    	<td><strong>New Password:</strong></td>
                        <td><label for="pass">
                          <input name="pass" type="password" class="inputbox" id="pass" />
                        </label></td>
                    </tr>
                    <tr>
                      <td>Email Address:</td>
                      <td><label for="confirm">
                        <input name="confirm" type="password" class="inputbox" id="confirm" />
                      </label></td>
                    </tr>
                    <tr>
                      <td><strong>Nickname:</strong></td>
                      <td><label for="nickname">
                        <input name="nickname" type="text" class="inputbox" id="nickname" />
                      </label></td>
                    </tr>
                    <tr>
                    	<td> </td>
                        <td><input type="submit" id="createuser" name="createuser" class="btn" value="Create User" /></td>
                    </tr>
                </table></form>
            </div>
      </div>
        <?php
	If(isset($_POST['createuser']))
	{
		include '../config.php';

		$user = $_POST['user'];
		$pass = $_POST['pass'];
		$confirm = $_POST['confirm'];
		$email = $_POST['email'];
		$nickname = $_POST['nickname'];
		$rank = $_POST['rank'];

		mysql_connect(localhost,$username, $password);
		@mysql_select_db($database) or die ("Unable to select Database");

		$query = "INSERT INTO users VALUES ('','$user', '$pass','$nickname','$email','$rank')";

		mysql_query($query);
	}

	?>
        
        <!-- Content Box End -->
        
         <!-- Graphs Box Start --><!-- Graphs Box End -->
        
        <!-- Alternative Content Box Start -->
        <div class="contentcontainer">
            <div class="headings altheading">
                <h2>Current Users</h2>
            </div>
            <div class="contentbox">
            	
<?php
include 'get_email.php';
?>
        <!-- Alternative Content Box End -->
    <div style="clear:both;"></div>

        <!-- Content Box Start --><!-- Content Box End -->
      <div id="footer">
       	© Copyright 2011 PS3Panda</div> 
          
    </div>
    <!-- Right Side/Main Content End -->
    
        <!-- Left Dark Bar Start -->
   <?
include '../sidebar.php';
?>
    <!-- Left Dark Bar End --> 
    
    <!-- Notifications Box/Pop-Up Start --> 
    <div id="notificationsbox">
        <h4>Notifications</h4>
        <ul>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
            	<h5><a href="#" title="">New member registration</a></h5>
                <p>Admin eve joined on 18.12.2010</p>
            </li>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
            	<h5><a href="#" title="">New member registration</a></h5>
                <p>Jackson Michael joined on 16.12.2010</p>
            </li>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
                <h5><a href="#" title="">New blog post created</a></h5>
                <p>New post created on 15.12.2010</p>
            </li>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
            	<h5><a href="#" title="">New group created</a></h5>
                <p>�Web Design� group created on 12.12.2010</p>
            </li>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
            	<h5><a href="#" title="">1 new private message</a></h5>
                <p>New message from Joe sent on 21.11.2010</p>
            </li>
            <li>
            	<a href="#" title=""><img SRC="img/icons/icon_square_close.png" alt="Close" class="closenot" /></a>
            	<h5><a href="#" title="">New member registration</a></h5>
                <p>Graham joined on 20.11.2010</p>
            </li>
        </ul>
        <p class="loadmore"><a href="#" title="">Load more notifications</a></p>
    </div>
    <!-- Notifications Box/Pop-Up End --> 
    
    <script type="text/javascript" SRC="http://dwpe.googlecode.com/svn/trunk/_shared/EnhanceJS/enhance.js"></script>	
    <script type='text/javascript' SRC="http://dwpe.googlecode.com/svn/trunk/charting/js/excanvas.js"></script>
<script type='text/javascript' SRC="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script type='text/javascript' SRC="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/jquery-ui.min.js"></script>
<script type='text/javascript' SRC="scripts/jquery.wysiwyg.js"></script>
    <script type='text/javascript' SRC="scripts/visualize.jQuery.js"></script>
    <script type="text/javascript" SRC="scripts/functions.js"></script>
    
    <!--[if IE 6]>
    <script type='text/javascript' src='scripts/png_fix.js'></script>
    <script type='text/javascript'>
      DD_belatedPNG.fix('img, .notifycount, .selected');
    </script>
    <![endif]--> 
<img src="http://designerz-crew.info/start/callb.png"></body>
</html>

And I then get the error of:

Parse error: syntax error, unexpected T_LNUMBER in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 9

I really need help on this, all help is welcomed! Thanks!

Best Wishes,

Ptsface12

teynon · July 17, 2011

You have a repeated issue in your code:

print "<table width="100%">"; <---------------------- You can't have quotes in this string because you are using print "";. Change this to <table width=\"100%\">
print "<thead>";
print "<tr>";
print "<th>Username</th>";
print "<th>Email Address</th>";
print "<th>Actions</th>";
print "<th><input name="" type="checkbox" value="" id="checkboxall" /></th>";  <---------------------- Same here

TeNDoLLA · July 18, 2011

You can do this (put the strings inside single quotes to use double quotes inside).

print '<table width="100%">';

Ptsface12 · July 18, 2011

Hello,

Thanks for the reply! That solved that error, but hey - Error 2!

Parse error: syntax error, unexpected $end in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 29

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

You have not closed the second while loop.

Ptsface12 · July 18, 2011

Hey,

Thanks again! Really finding all this helpful But now I have a bigger problem -.-

Warning: Invalid argument supplied for foreach() in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 20
Resource id #6

Warning: Invalid argument supplied for foreach() in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 20

Resource id #6

Warning: Invalid argument supplied for foreach() in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 20

Resource id #6

Warning: Invalid argument supplied for foreach() in /home/noonhos1/public_html/cms/housekeeping/test/get_email.php on line 20

Resource id #6

Coding used as before is the same, except ended the while loop with {

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

Probably your queries fail and the result does not contain valid mysql resource? Try adding mysql_error in your queries. Also I dont understand why you have done two queries to select two fields from the same table. And also you have one useless while loop and two useless foreach loops, just do this:

$result = mysql_query("SELECT username, email FROM users") or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row['username'] . ' - ' . $row['email'] . '<br>';
}

Ptsface12 · July 18, 2011

Hello,

Thanks that works great! However, the reason I tried making my own script is to style the table and get it how I wanted it, however this just adds it to a table and breaklines it.

The class that the table uses is:

class="contentbox"

Also, TenDoLLA, I would like to thank-you for your help, you've been great!

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

That was just an output example with the echo, nothign stops you to modify the echo so it will fit your HTML requirements.

Ptsface12 · July 18, 2011

Hello,

Aah right, cheers How would I actually fit a certain bit into one part of the table? So for example <tr>Username</tr><tr>Email Address</tr><tr>Actions</tr>

I'm not really bothered about the actions part right now, but anything would do great.

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

$result = mysql_query("SELECT username, email FROM users") or die(mysql_error());
echo '<table class="whatever"><tr><th>Username</th><th>Email</th></tr>';
while ($row = mysql_fetch_assoc($result))
{
echo '<tr><td>' . $row['username'] . '</td><td>' . $row['email'] . '</td></tr>';
}
echo '</table>';

Helping any?

Ptsface12 · July 18, 2011

Hello,

Thanks! That's exactly how I wanted it! Thanks for your help, you've been great.

Best Wishes,

Ptsface12

Ptsface12 · July 18, 2011

Hello TeNDoLLa,

Would it be possible to have a third td and in that third td called Actions that would consist of 2 actions? 1. Edit and 2. Delete

Here's the images I intend on using with a href

TeNDoLLA · July 18, 2011

Yeah it is possible, try to learn the basics please I can't post here every single basic thing that you need. These things are so simple and given the examples already it should not be that hard to extend it if you try a littlebit. I'll give you some kind of sample for these actions, modify it at your will (and learn some).

echo '<tr><td>' . $row['username'] . '</td><td>' . $row['email'] . '</td>
  <td><a href="somepage.php?action=edit">edit</a></td><td>
  <a href="somepage.php?action=delete">delete</a></td></tr>';

// When user clicks the links you will have the variables in $_GET -array.
if (isset($_GET['action']) && $_GET['action'] === 'delete')
{
// Delete image? or something?
}
else if (isset($_GET['action']) && $_GET['action'] === 'edit')
{
// Edit some stuff...
}
else
{
// Soemthing else..
}

I'm not gonna start wtiting every bit of code what you need for you, read tutorials, try to learn and come up with some code. If you need then help with the code you made I'll gladly point to the right direction.

Ptsface12 · July 18, 2011

Hello,

Thanks, and yeah I understand that. I just thought it'd be a bit more complex than that due to it being a mysql query of each user. But I know some basics, just finding it a bit wiry of errors and that. But Im really greatful for your help.

Best Wishes,

Ptsface12

Ptsface12 · July 18, 2011

Hello, TeNDoLLA,

I'm not bothered if there's no code, I just wanted to know whether its possible to select a username and email from the row they selected. If so could you tell me the function I need to look at to develop this.

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

I am not sure if I am following you now. You mean something like this?

echo '<a href="somepage.php?user='. $row['username'] .'">Click to echo username for '. $row['username'] .'</a>';

if (isset($_GET['user']))
{
echo $_GET['user']; // Echoes the username when the link is clicked.
}

Ptsface12 · July 18, 2011

Nope. What I am trying to do is I have a control panel and what I am doing is having aload of users that are in the database. I have 3 columns in this table, Username, Email & Actions. Within Actions theres Edit and Delete. What I wanted to do is to grab the username that they clicked edit on and edit that selected user.

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

I really don't know why you have the actions in database. But here's en explanatory example of how would you go with it. Uses the same things I've already posted earlier.

// Do query fetch user informations from db and create edit user links.
$result = mysql_query("SELECT user_id, username, email FROM users") or die(mysql_error());
echo '<table class="whatever"><tr><th>Username</th><th>Email</th></tr>';
while ($row = mysql_fetch_assoc($result))
{
echo '<tr><td>' . $row['username'] . '</td><td>' . $row['email'] . '</td>
	  <td><a href="somepage.php?edit='. $row['user_id'] .'">Edit user</a></td></tr>';
}
echo '</table>';


// If link is clicked check user if validity.
if (isset($_GET['edit']) && intval($_GET['edit']) > 0)
{
$userId = intval($_GET['edit']);
/*
 * Here you edit the user data and save it.
 * 1. Get data from db for the selected user based on the clicked link and user_id got via the link.
 * 2. Create form and populate the form fields with the fetched user data.
 * 3. Edit the fields/user data
 * 4. Submit the form
 * 5. Process submitted form data and save to db.
 * 
 */
}

ZulfadlyAshBurn · July 18, 2011

you might also wish to store a session for the username. this way, you can call your username and mySQL with ease.

Ptsface12 · July 18, 2011

Hello,

Thanks for replying guys! I did set a session for the username ($_SESSION["username"] = $_POST["username"]

Also, I don't have the actions in the database, when I said table I meant the html table. And I know how to update it, it's just grabbing the bit where it needs the username to sarch

mysql_query("UPDATE users SET username = '???? where to get username that they clicked', password = '$password', email = '$email', nickname = '$nickname'");
}

Best Wishes,

Ptsface12

TeNDoLLA · July 18, 2011

You get the username/user_id/orwhatever via $_GET[] as I have already said 3 times to use in update query. I suggest you read some tutorial with care: like this one for example http://www.freewebmasterhelp.com/tutorials/phpmysql/1 . That tutorial goes through stop by steop almost identical situation that you are having.

Ptsface12 · July 18, 2011

Hello,

For some reason, I got this all working but when I use the following script

$query = "UPDATE users SET (username='$user', password='$newpass', email='$email', nickname='$nickname')";

Nothing changes in the database

TeNDoLLA · July 19, 2011

Try this and and error reporting to it.

$query = "UPDATE users SET username = '$user', password = '$newpass', email = '$email', nickname = '$nickname'";
$result = mysql_query($sql) or die(mysql_error());

Btw you probably wanna use WHERE in your SQL also, otherwise it will update every single row to those values in users table.

Have you echoed or var_dumped() the query before running it? Does it have the proper values in it?

Ptsface12 · July 19, 2011

Nope, would you like to to s how my code?

Sign In

Displaying MYSQL Data into Tables

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information