PHP - Pdo Select Query With Unique Limit. Secure?

I am building a commenting system, it's sort of like facebooks comments. I have it right now so it loads the first last 3 comments in the database, and then there's a button to view all comments. I have it working fine, except when you add another comment to it, and then click the view all comments,


This is the script that loads onload showing the last 3 comments


$limit  = 3;

$query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$id' ORDER BY id DESC LIMIT $limit");



This is the php script that runs when you click view more




//($start_id would be 3 in this case)

$query5 = mysql_query("SELECT * FROM sub_comments WHERE status_id='$comment_id' ORDER BY id DESC LIMIT $start_id, 1000");

so to try to show you what's happening:

OnLoad--------------------------------|---When you add more comments, (let's just say u add 1 comment before u click view comments)----
comment 9   --------------------------|   comment 10
comment 8   --------------------------|   comment 9
comment 7   --------------------------|   comment 8
------------------------------------------|   comment 7
----to be loaded onclick-------------|------What's loaded onclick---
comment 6   --------------------------|   comment 7
comment 5   --------------------------|   comment 6
comment 4   --------------------------|   comment 5
comment 3   --------------------------|   comment 4
comment 2   --------------------------|   comment 3
comment 1   --------------------------|   comment 2
------------------------------------------|   comment 1


(see how "comment 7" is being shown twice in a row)

Hi. I'm trying to make a sort of a forum, and this is the part of the code that needs to be changed, only I don't know how.
Code: [Select]
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
// OREDER BY id DESC is order result by descending
$result=mysql_query($sql);
?>
<table width='50%' border="0" align="center" cellpadding="3" cellspacing="1">
<?php
$limit=10;
$page=0;
$numrows = mysql_num_rows($result);
$pages = intval($numrows/$limit);
if ($numrows % $limit) {
$pages++;}
$current = ($page/$limit) + 1;
if (($pages < 1) || ($pages == 0)) {
$total = 1;}
else {
$total = $pages;}
$result = mysql_query("SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $page,$limit");
while($rows=mysql_fetch_array($result)){?>
<tr><td><font size="+2"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></font></td>
<td width='0*'><? echo $rows['datetime']; ?></td></tr>
<tr><td><? echo substr($rows['detail'], 0, 250)."..."; ?>&nbsp;&nbsp;<a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo "Citeste mai mult";} ?></a>
</td></tr>
<tr><tr><td>
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    \n");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b>\n");} // If current page don't give link, just text.
 else{
 echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>");}
?>
</td></tr></tr><?php
mysql_close();
?>
</table>

I made a database with 4 fields and 11 entries. The script shows the last 10 entries just as it should, but when I click on the 'next' button, nothing happens (it should show the first entry, but it just refreshes the page, and still the last 10 entries/1st page are shown). I think it has something to do with the link, but I can't figure out what exactly.
Can someone help, or at least give me a hint? Thanks in advance. Bye.

I'm trying to do an sql query from php that includes 15 condition "groups" each consisting of 4 conditions.  I have tried separating each condition group with brackets but still can't get it to work.  Am I missing something?

Thanks as always for everyone's help!!  Tom

Here is the code shown with line breaks for clarity:


$query="SELECT * FROM d4dauctions

WHERE (`user gender` LIKE '%$gender%'
AND `auction area` LIKE '%$auctionarea%'
AND `user age group` LIKE '%$agegroup%'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$usergender' = 'Either'
AND `auction area` LIKE '%$auctionarea%'
AND `user age group` LIKE '%$agegroup%'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$auctionarea' = 'Anywhere'
AND `user gender` LIKE '%$gender%'
AND `user age group` LIKE '%$agegroup%'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$agegroup' = 'Any age'
AND `user gender` LIKE '%$gender%'
AND `auction area` LIKE '%$auctionarea%'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$typeofbid' = 'Any type of bid'
AND `user gender` LIKE '%$gender%'
AND `auction area` LIKE '%$auctionarea%'
AND `user age group` LIKE '%$agegroup%')

OR  ('$usergender' = 'Either'
AND '$auctionarea' = 'Anywhere'
AND `user age group` LIKE '%$agegroup%'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$usergender' = 'Either'
AND `auction area` LIKE '%$auctionarea%'
AND '$agegroup' = 'Any age'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$usergender' = 'Either'
AND `auction area` LIKE '%$auctionarea%'
AND `user age group` LIKE '%$agegroup%'
AND '$typeofbid' = 'Any type of bid')

OR  (`user gender` LIKE '%$gender%'
AND '$auctionarea' = 'Anywhere'
AND '$agegroup' = 'Any age'
AND `type of bid` LIKE '%$typeofbid%')

OR  (`user gender` LIKE '%$gender%'
AND '$auctionarea' = 'Anywhere'
AND `user age group` LIKE '%$agegroup%'
AND '$typeofbid' = 'Any type of bid')

OR  (`user gender` LIKE '%$gender%'
AND `auction area` LIKE '%$auctionarea%'
AND '$agegroup' = 'Any age'
AND '$typeofbid' = 'Any type of bid')

OR  ('$usergender' = 'Either'
AND '$auctionarea' = 'Anywhere'
AND '$agegroup' = 'Any age'
AND `type of bid` LIKE '%$typeofbid%')

OR  ('$usergender' = 'Either'
AND `auction area` LIKE '%$auctionarea%'
AND '$agegroup' = 'Any age'
AND '$typeofbid' = 'Any type of bid')

OR  ('$usergender' = 'Either'
AND '$auctionarea' = 'Anywhere'
AND `user age group` LIKE '%$agegroup%'
AND '$typeofbid' = 'Any type of bid')

OR  ('$usergender' = 'Either'
AND '$auctionarea' = 'Anywhere'
AND '$agegroup' = 'Any age'
AND '$typeofbid' = 'Any type of bid')

ORDER BY RANK DESC
";

Hello,

I need to interrupt the execution of an oracle query if it is taking more than 10 seconds, and give user a message informing him about execution timeout. I googled a lot but i didn't find anything useful. Is there any way to set a time limit to oci_execute.

I cannot modify DB settings...

Any Idea?

Thnks

I have these two tables... schedule (gameid, homeid, awayid, weekno, seasonno) teams (teamid, location, nickname) This mysql query below gets me schedule info for ALL 32 teams in an array...

$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else { 
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}

However, I only want to get info for one specific team, which is stored in the $teamid variable. This should be very easy, right? I have tried multiple things, including this one below (where I added an AND statement of "AND (h.teamid=$teamid OR a.teamid=$teamid)"), but this one still outputs too much...

$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014
AND (h.teamid=$teamid OR a.teamid=$teamid)";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else { 
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}

Below is the array that the above outputs. In a nutshell, all I want is that 1st array ([1]) which has, in this example, the Eagles full schedule. It's not giving me too much else and I guess I could live with it and just ignore the other stuff, but I'd rather be as efficient as possible and only get what I need...

Array
(
[1] => Array
(
[1] => Jaguars
[2] => @ Colts
[3] => Redskins
[4] => @ 49ers
[5] => Rams
[6] => Giants
[7] => BYE
[8] => @ Cardinals
[9] => @ Texans
[10] => Panthers
[11] => @ Packers
[12] => Titans
[13] => @ Cowboys
[14] => Seahawks
[15] => Cowboys
[16] => @ Redskins
[17] => @ Giants
)

[27] => Array
(
[1] => @ Eagles
)

[28] => Array
(
[2] => Eagles
)

[4] => Array
(
[3] => @ Eagles
[16] => Eagles
)

[14] => Array
(
[4] => Eagles
)

[15] => Array
(
[5] => @ Eagles
)

[3] => Array
(
[6] => @ Eagles
[17] => Eagles
)

[] => Array
(
[7] => @ Eagles
)

[16] => Array
(
[8] => Eagles
)

[25] => Array
(
[9] => Eagles
)

[11] => Array
(
[10] => @ Eagles
)

[7] => Array
(
[11] => Eagles
)

[26] => Array
(
[12] => @ Eagles
)

[2] => Array
(
[13] => Eagles
[15] => @ Eagles
)

[13] => Array
(
[14] => @ Eagles
)

)

Hello all,   Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).   The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).   Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:   In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).   I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.   Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.   So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:   1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red. 2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red. 3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.   Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.   Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.   Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.   Cheers guys!

case "update-delete":
	if(isset($_POST['highlight-purchased'])) 
		{
			// ****** Setup customized query to obtain only items that are checked ******
			$sql = "SELECT * FROM shoplist WHERE";
					for($i=0; $i < count($_POST['checkboxes']); $i++)
						{
							$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
						}
					$sql= rtrim($sql, "or");
											
			$statement = $conn->prepare($sql);
			$statement->execute();
																						
			// *** fetch results for all checked items (1st query) *** //
			$result = $statement->fetchAll();

			$statement->closeCursor();
											
			// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
			$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
											
			// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
			$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";

			$statementSetToN = $conn->prepare($sqlSetToN);
			$statementSetToY = $conn->prepare($sqlSetToY);
											
			/*** loop through the results/collection of checked items ***/
			foreach($result as $row)
				{
					if ($row["purchased"] != "Y")
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToY = $statementSetToY->fetch();
															
							foreach($resultSetToY as $row)
								{															
									$statementSetToY->execute();																
								}
						}
					else
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToN = $statementSetToN->fetch();
															
							foreach($resultSetToN as $row)
								{															
									$statementSetToN->execute();																
								}
						}
				}
				break;
		}

 CRUD Queston.png   20.68KB   0 downloads    

I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp. 
I have added another column called `'unique_code' (varchar(64), utf8_unicode_ci)`.

What I would very much appreciate assistance with is;

a) Generating a 5 digit alphanumeric code, ie: 5ABH6 
b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again 
c) Insert the uniquely generated 5 digit alphanumeric code into `'unique_code'` column, corresponding to the email address just entered. 
d) display the code on screen.

What code must I put and where?


**My current php is as follows:**

Code: [Select]
    require "includes/connect.php";
   
    $msg = '';
   
    if($_POST['email']){
   
    // Requested with AJAX:
    $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');
   
    try{
    if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
    throw new Exception('Invalid Email!');
    }
   
    $mysqli->query("INSERT INTO coming_soon_emails
    SET email='".$mysqli->real_escape_string($_POST['email'])."'");
   
    if($mysqli->affected_rows != 1){
    throw new Exception('You are already on the notification list.');
    }
   
    if($ajax){
    die('{"status":1}');
    }
   
    $msg = "Thank you!";
   
    }
    catch (Exception $e){
   
    if($ajax){
    die(json_encode(array('error'=>$e->getMessage())));
    }
   
    $msg = $e->getMessage();
    }
    }

Unless buffer overflows or breaking out of code to perform a new command are problems that have been solved....

I am trying to figure out the proper PHP method for setting a boundary on a variable within a script. I have this variable $name which is fed a value from $_POST['name'] from a form field. Now this form field is limited in the HTML to accept only 20 characters, but someone could easily edit the form or outgoing post data. So I want to know how to limit the variable size in the script. In other languages it could be something like this: var name(20). So how do I do that in PHP?

Hi, Everyone! This my first time posting here. Anyways, I've been having problem getting the correct data from my database. I want to select the total_on/off_hours,exposure,plate_number, and terminal_status. But when I select the terminal_status, there are duplicates, I already used the DISTINCT function. It's a little hard to explain so, I'll just show you.   Here's a screen cap of my code trying to get just the terminal_status       What's the problem? What should I do? Any suggestions/help will be much appreciated.

I have this code below:

Code: [Select]
members_count <= ".$diff." || members_count >= ".$defmemcount."
My test says $diff = 6 and $defmemcount = 11, members_count = 14

So how come this returns as if members_count isn't less or equal to 6 OR higher or equal to 11?

i am unable to select total_comment from mysql table related to each post..   For detail information check http://stackoverflow...-query-in-mysql

ok, so I need to know how I can perform an operation of $total equaling $price1 + $price2 etc.... if they are not empty in the database............... does this make sense?

Hi

I need to do a SELECT query like

$query = "SELECT name FROM myusers WHERE myidnum = ".$_SESSION['myid'].""; 

and put the results into an array like

$myusers = array("David","John","Lucy","Sarah");

But I cannot figure out how to do this

Can anyone help?

Thanks

Hi,

i'm trying to select some entries from my DB, i don't quite understand what's the problem here, i'm using this:

Code: [Select]
//Customers Online
$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE status = 1";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $customers_online = $row[0];
}
Which works fine, now i want to select by country and i'm doing this:

Code: [Select]
//Customers DE
   $query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de'";
   $queryerg = mysql_query($query) OR die(mysql_error());
   while($row = mysql_fetch_array($queryerg)){
     $customers_de = $row[0];
   }
Which doesn't work? If i print / echo $customers_de i just get a blank value, not even 0 or anything just blank.

Any help ?

Hi there I am new to this forum and not much of a php expert. I hope you can assist me. My problem is as follows: I have a column consisting of several values (in this case: sceience, art, humanistics and music) I would like to use the following mysqli query in order to count the appearances of each value in this column:     $query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2"   I have tried lots of ways to loop the results but couldn't get the it working. Can anyone suggest any idea?   Thanks   Hanan