I'm having a little trouble with a page in a website I'm prototyping for a speech. I can get one of my queries to return successfully, but the other returns empty results for a table that definitely has information in it. I've tried simplifying it even more than it is, but it still won't work. I don't know why.
The table is called comments and it is formatted as follows:
comment_id: integer(255), primary key, not null, unsigned int, auto_increment
group_id: integer(10), external key, not null, unsigned int
comment: text, not null
time: timestamp, default is current_timestamp, on update current_timestamp, not null
My php code looks like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
<?php
//restrict access to page
$root = "../";
session_start();
if(!isset($_SESSION["username"]))
{
header("Location: " . $root . "admin/login.php");
}
//set title
$title = "View Suggestions";
//limit number of suggestions and length
//of suggestions displayed
$pageLimit = 40;
$charLimit = 30;
//open databases
require_once($root . "databases/data.php");
//set count
$count = 1;
//get number of suggestions in comments table
$cquery = mysqli_query($cbase, "SELECT COUNT(*) AS count FROM comments"); //works correctly
$c = mysqli_fetch_array($cquery);
$count = $c["count"];
//calculate maximum number of pages
$pages = (int)($count / $pageLimit + 1);
//get current page
$page = 1;
if(isset($_GET["page"]))
{
$page = $_GET["page"];
}
//restrict page viewing to valid range
if($page > $pages)
{
$page = $pages;
}
if($page <= 0)
{
$page = 0;
}
//set failed
$failed = false;
//get suggestions for specific or all
//groups depending on user
//neither query works
if($_SESSION["group_id"] != NULL)
{
$query = mysqli_query($cbase, "SELECT * FROM comments WHERE group_id=" . $_SESSION["group_id"] . " ORDER BY time DESC LIMIT " . (($page - 1) * $pageLimit) . " " . $pageLimit);
}
else
{
$query = mysqli_query($cbase, "SELECT * FROM comments ORDER BY time DESC LIMIT " . (($page - 1) * $pageLimit) . " " . $pageLimit);
}
//validate query
if($query === false)
{
$failed = true;
}
//start page
require_once($root . "layout_start.php");
//query failed
if($failed)
{
//notify user
echo "<p>Failed to load suggestions (There may be no suggestions at this time).</p>";
}
?>
<table border="1px" cellpadding="3px" cellspacing="0" style="margin: 20px auto;">
<?php
//get all comments from query
while(!$failed && $array = mysqli_fetch_array($result))
{
//format elements
//$date = strtotime($array["time"]);
//$date = date("m/d/Y H:i", $date);
$date = date("m/d/Y H:i", $array["time"]);
$id = $array["comment_id"];
//prevent invalid elements
if($id == NULL)
{
break;
}
//display elements
?>
<tr>
<td><a href="<?php echo $root . "admin/view.php?comment=" . $id . "&page=" . $page; ?>"><?php echo substr($array["comment"], 0, $charLimit) . (strlen($array["comment"]) > $charLimit ? ". . ." : ""); ?></a></td>
<td><a href="<?php echo $root . "admin/view.php?comment=" . $id . "&page=" . $page; ?>"><?php echo $date; ?></a></td>
</tr>
<?php
}
?>
</table>
<p>
<?php
//display previous/first arrows
if($page > 1)
{
?><a href="<?php echo $root . "admin/comments.php?page=1";?>"><<</a>
<a href="<?php echo $root . "admin/comments.php?page=" . ($page - 1);?>"><</a>
<?php
}
//display up to two pages to the left
//or right of current page
for($x = $page - 2; $x <= $page + 2; $x++)
{
if($x > 0 && $x <= $pages)
{
?>
<a href="<?php echo $root . "admin/comments.php?page=" . $x;?>"><?php echo $x;?></a>
<?php
}
}
//display next/last arrows
if($page < $pages)
{
?>
<a href="<?php echo $root . "admin/comments.php?page=" . ($page + 1);?>">></a>
<a href="<?php echo $root . "admin/comments.php?page=" . $pages;?>">>></a>
<?php
}
?>
</p>
<a href="<?php echo $root . "admin/index.php";?>">Back</a>
<?php
//end page
require_once($root . "layout_end.php");
?>
|
I'm not sure what I did wrong and was hoping somebody else would be able to point something out. Thanks for all your help!
