PHP - Problem: Mysql Tables Rows - One Row Per Page

Hi Everyone,

I am working on developing a blog/comment system for my website. I have 3 tables, the first a blog post table with a row/primary key of id that is auto incremented as each new article is created via a form. The second table is a comments table with a row of article_id and the third table is a category table with an article_id row as well.

From the main blog page I have links that will take users to each individual blog post using $_GET like so:

Code: [Select]
http://www.mydomain.com/blog/article/?id={$row['id']}
From here I would like to be able to display the comment for that particular article below the article and also have a sidebar with links to blog categories which will then display each post according to whether they are in that category or not.

My question is two-fold:

First, what is the best way to pull the id field from the blog/post table, form, or url and enter it into the article_id rows of the second two tables? Should I use a hidden field, the URL id via $_GET, using a join, or what?

Secondly:

What is the best way to compare and display those rows so that the appropriate comments are being shown for the correct article, etc.

This is my first time doing this so I am grateful for any help or suggestions.

Thanks,

kaiman

Hi,

So i'm having a little trouble trying to simplify some code I already have.

Code: [Select]
$result = mysql_query("SELECT images, cat_id FROM image_cat");

while ($row=mysql_fetch_array($result))
{
$cat_id=$row['cat_id'];
echo $cat_id;
$imgresult = mysql_query("SELECT * FROM images WHERE cat_id='".$cat_id."'");
while ($col=mysql_fetch_array($imgresult))
{
echo $col['img_id'];
}
}

Okay, so this works, however i'm sure I can bring it together under one query, though i'm truly stumped as to how?
I thought I could do a simple join:

Code: [Select]
$result = mysql_query("SELECT image_cat.cat_id, images.img_id WHERE image_cat.cat_id=images.image_cat");

This works, but repeats the image category on each loop. I could remove this via php if statements, but there must be a simpler/mysql solution.

If it doesn't make any sense I need to get

Img cat 1
-img 1
-img 2

img cat 2
-img 3
-img 4

etc etc

That join will give

img cat 1
-img 1
img cat 1
-img 2
img cat 2
-img 3

Hope that makes sense?? And any help would be amazing!
Thanks.

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

I have a cart script, and I need to display the products the user has added.  This is a school assignment and I'm very stuck on this seemingly 'little' error.

Right now, I have two tables:

For time's sake, I am showing duplicate products with different prod_id's:
Table: products
prod_id category sub_category thumb_href image_href title desc summary manufacturer price 17 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 18 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99 19 sofa leather red_leather_sofa_small.jpg red_leather_sofa_main.jpg Red Leather Sofa [BLOB - 134B] [BLOB - 59B] Balenty 999.99
And this is the cart table that holds temporary cart items (storing the product ID to pull later):
Table: cart
temp_cart_item cart_id (assigned by session_id() ) prod_id user_id 22 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 23 jier11u0e7cl2ghosjodpaark2 17 asdfasdf 24 jier11u0e7cl2ghosjodpaark2 35 asdfasdf 25 jier11u0e7cl2ghosjodpaark2 5 asdfasdf 26 jier11u0e7cl2ghosjodpaark2 19 asdfasdf
SO, user 'asdfasdf' is logged in and has selected 5 items for his cart - two of which are the same (prod_id 17).  Basically,

I need to compare the prod_id's of each table and output all products (and quantities) matching the temporary cart prod_id.

What I have now to loop through and display the cart items are (bear with me):


echo "<h1>Cart</h1>";
}
// Display welcome and navigation links
displayWelcome();
displayNav();

// Getting the prod_id's from the table cart to compare with the product table
$cart_id = session_id();
$user_id = ($_SESSION['login_username']);
$query = "SELECT prod_id FROM cart WHERE cart_id='$cart_id' AND user_id='$user_id'";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$nrows = mysqli_num_rows($result);
if($nrows < 1)
{
echo "<h2>Your cart is empty</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
}
else {  // Continue to gather products for all the product IDs in the products DB table...
echo "<h4>$nrows items in your cart</h4>";
while($row = mysqli_fetch_array( $result )) {
$user_id = ($_SESSION['login_username']);
$product = $row['prod_id'];

// Is the query my problem?
$query = "SELECT * FROM products WHERE prod_id='$product'";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$nrows = mysqli_num_rows($result);
if($nrows < 1) { // If the item doesn't exist, display an error
echo "<h2>That is not a valid product, or any of the items you had in your cart are missing or have been deleted.</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
// Protect from continually displaying an error by deleting the invalid cart record
$deletequery = "DELETE FROM cart WHERE prod_id='$product'";
$deleteresult = mysqli_query($cxn,$deletequery) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
exit();
}
while($row = mysqli_fetch_array( $result )) {  //  Then disply them in a table
echo "<h2>Items in Your Cart:</h2>";
echo "
<table id=\"products\">
<thead>
<tr>
<th class=\"col1 headercol\" scope=\"col\">Thumbnail</th>
<th class=\"col2 headercol\" scope=\"col\">Summary</th>
<th class=\"col3 headercol\" scope=\"col\">Manufacturer</th>
<th class=\"col4 headercol\" scope=\"col\">Price</th>
</tr>
</thead>

<tbody>\n";
$query = "SELECT SUM(price) FROM products WHERE prod_id='$product' GROUP BY price";
$priceResult = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
// keeps getting the next row until there are no more to get
// Print out the contents of each row into a table
echo "<tr>
<td class=\"col1\">"; 
echo "<a class=\"imgHref\" href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\"><img src=\"../images/".$row['thumb_href']."\" alt=\"".$row['title']."\" /></a>";
echo "</td>\n"; 
echo " <td class=\"col2\">"; 
echo "<h3 class=\"title\"><a href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\">".$row['title']."</a> <span class=\"inlineh3\">by <a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></span></h3>";
echo "<p class=\"summary\">".$row['summary']."</p>
<p class=\"descHeading\">&#38;#9758; <em>Full Description:</em></p>
<p class=\"desc\">".$row['desc']."</p>";
echo "</td>\n"; 
echo " <td class=\"col3\">"; 
echo "<p class=\"manufacturer\"><a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></p>";
echo "</td>\n"; 
echo " <td class=\"col4\">"; 
echo "<p class=\"price\">$".$row['price']."</p>";
echo "</td>
</tr>\n"; 
}
while($priceRow = mysqli_fetch_array( $priceResult )) {
echo "<tr class=\"short\"><td class=\"col1\"><h3><a href=\"clear_cart.php\">Empty Cart</a></h3></td><td class=\"col2\"></td><td class=\"col3 nobackground\">Total:</td><td class=\"col4\">$".$priceRow[0]."</td></tr>";
}
echo "</tbody>
</table>";
echo "<p class=\"submitOrder\"><form action='submit_order.php' method='post' id='form'>
  <input type='submit' name='pButton' value='Submit Order'>
  <input name='order_total' type='hidden' id='order_total' value='".$priceRow[0]."' />
</form>
</p>";
}
}



And so the cart.php looks something like this (this example asdfasdf has 6 items in his cart):



So you can see that it only displays ONE item, even though asdfasdf DOES have 6 items in his cart (proven by checking database).  And the price is not accurate, either.

Can anyone help me in where my problem might be?  Is it my query?  Or my while clause?

I am trying to write some data from multiple SQL tables to a page. In the first table is a list of places. I then have more tables that are named after the different places. For example, say my first place in the list is called Place1. I have a table named Place1 with data that corresponds to place1. The data contained in the table named Place1 is a list of things to do in this place. It has 21 columns and each one is something to do in the morning, afternoon, and at night for each day of the week in the place Place1. What I am trying to do is display a sort of weekly calendar as a table on a webpage that lists all of the places in one column and then lists seven days of the week as 7 more columns. Then in each data cell I would want to list the things to do in the morning, afternoon and at night for the certain day of the week and for the place. The problem is that I am creating a CMS to allow other users with no coding knowledge to update events for other places, so I have to display data that could have been altered.

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

I don't know if that would even work and if it did, I know it cannot be the best way to do this. I am pretty stuck here and don't really have a good solution so if anyone proposes a solution that is radically different to anything I have done, I am open to everything. Thank you!

im wondering how to get php to read the latest row (instead of show them all) and when another one comes it replaces it once the page refreshes

Here's the error: Code: [Select]
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/stayway1/public_html/www.one-mafia.com/eng/inboxread.php on line 99
Here's lines 90-110: (99 in bold)

Code: [Select]
if (strip_tags($_POST['Accept_OC'])){
$oc_id=strip_tags($_POST['oc_id']);
if (strip_tags($_POST['place']) == "we"){
$use="we_inv";
$a="we";
$query= "SELECT * FROM oc WHERE we_inv='$username' AND id='$oc_id'";
}elseif (strip_tags($_POST['place']) == "ee"){
$use="ee_inv";
$a="ee";
$query= "SELECT * FROM oc WHERE ee_inv='$username' AND id='$oc_id'";

}elseif (strip_tags($_POST['place']) == "driver"){
$use="driver_inv";
$a="driver";
$query= "SELECT * FROM oc WHERE driver_inv='$username' AND id='$oc_id'";
}

$round=mysql_query($query);
[b]$check=mysql_num_rows($round);[/b]
if ($check != "0"){
echo "You are now in that OC";
mysql_query("UPDATE `oc` SET `$a`='$username' WHERE `id`='$oc_id'");
mysql_query("UPDATE `users` SET `oc`='1' WHERE `username`='$username'");
}
}
if (strip_tags($_POST['Yes_street'])){
$race_id=strip_tags($_POST['race_id']);
if ($fetch->street != "0"){ echo "Your in a race."; }elseif ($fetch->street == "0"){

if ($fetch->last_race >= time()){ echo "You cant do anouther race yet."; }elseif ($fetch->last_race < time()){

I have a HTML dropdown list which is populated from a MySQL table. I use an auto-incremend ID field. I have also got a sort_order field.
 
I would like to be able to specify the exact order that entries should appear in the dropdown list. Specifically I need some functionality that does the following two things:
 
1) If I change the sort order of a record to for example "4" then the existing record with sort_order 4 becomes 5, sort_order 5 becomes 6 and so on.
 
2) If I change the sort order of a record to for example "4" and there is currently no record with sort_order 4, then no other records will be updated.

First off I know this will be a problem with my code but I honestly have looked at it for too long. If anyone can shed a bit of simple light on it that would be great.

$count should return about 8 or 9 or so but just returns 1 - I can only print out the number 1 if I do a direct echo of the mysql_num_rows() instead of putting it into the $count variable. Its really confusing, the database is all set up correctly as earlier on in the page I get all the information from the database and print out a table with it. There is something wrong here!

Problem only occurs when $count = mysql_num_rows($result1); is written.

Code: [Select]
//Following user presses button with name applyChanges.

if(isset($_POST['applyChanges']))
{
$result1 = mysql_query("SELECT * FROM music") or die ("Could not get result1");
$count = mysql_num_rows($result1) or die ("Could not count rows"); //Changed this to an echo without $count and prints a 1. - Should be greater than 8
echo "$count";

for($i=0; $i<$count; $i++)
{
$query2 = "UPDATE music SET name='$name[$i]' WHERE path='$path[$i]'";
$result2 = mysql_query($query2);
}
}
Thanks,
Matt

I have a MySQL query that returns rows containing date_add column like below:

DATE(date_add)
2014-01-07
2014-01-07
2014-01-07
2014-01-07
2014-01-08
2014-01-15
2014-01-20
2014-01-20
2014-01-20
2014-01-26
2014-01-28
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-16
2014-02-18
2014-02-18
2014-03-08
2014-03-08

How can I have pagination based on dates and show rows with the same date in a page and not just based on predefined limit number?

How would I make a script that would delete any row in a MySQL table that was more than 7 days old?

Now he sayes other fold:



Wrong parameter count for mysql_num_rows() in /home/a5078111/public_html/registerpage.html on line 146

Ok I am trying to delete some rows from a database. I have done this before with success so I thought I could use the same method just modify it a little and it would work. Man was I wrong. Any help on this is much appreciated below is my code.

Code for the Query to get the data from the database and for displaying the results in a table:
Code: [Select]

//GET DATA
$sql = "SELECT * FROM `".$tblname."` ORDER BY $orderby $sort LIMIT $startrow,$limit";
$result = mysql_query($sql) or die(mysql_error());
$result2 = mysql_query($sql);

//START TABLE AND TABLE HEADER
echo "<form name='form1' method='post' action=''><table style='font-size:9.5px;'>\n<tr><th>Delete</th>";
$array = mysql_fetch_assoc($result);
foreach ($array as $key=>$value) {
if($config['nicefields']){
$field = str_replace("_"," ",$key);
$field = ucwords($field);
}

$field = columnSortArrows($key,$field,$orderby,$sort);
echo "<th>" . $field . "</th>\n";
}
echo "</tr>\n";

//reset result pointer
mysql_data_seek($result,0);

//start first row style
$tr_class = "class='odd'";

//LOOP TABLE ROWS
while($row = mysql_fetch_assoc($result)){


echo "<tr ".$tr_class.">\n<td><a
 href='remove_rec.php?id=". $rows['ID'] . "'><p>Delete</p></a>";
echo "</td>";
foreach ($row as $field=>$value) {
echo "<td>" . $value . "</td>\n";
}
echo "</tr>\n";
//switch row style
if($tr_class == "class='odd'"){
$tr_class = "class='even'";
}else{
$tr_class = "class='odd'";
}

}

//END TABLE
echo "</table>\n</form>";

Here is the code that should delete the row:
Code: [Select]
$tblname = 'tablename';
// get value of id that sent from address bar
$id=$_GET['ID'];

// Delete data in mysql from row that has this id
$sql="DELETE FROM $tblname WHERE ID='$id'";
$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='leads3.php'>Back to Results</a>";
}

else {
echo "ERROR";
}

When I click on the link to delete a record it redirects me to the appropriate page with the "Deleted Successfully" message but when I go to view the results the row was not deleted. Any help on this would, again, be greatly appreciated.

Hey guys,

Currently Im using the code:

while($row=mysql_fetch_array($nt)){

echo"
$row[user_login],$row[user_email],$row[CallHistoryTimes],$row[CallHistoryInfo],$row[CallHistoryLastOp]$row[CallHistoryLastDate],<br>";
}

To list a mysql array; what I am trying to do is to use the mysql row ID and turn the entire string above into a list.

So for example, the database would get the ID information & create a link of edit.php/?id=3

If someone could help me on this it would be mega appreciated.

Thanks

So I have an array of IDs that I would only like to select from a database table instead of everything in the table.

How might I go about doing that?