Search the Community

Hi All, I am adding a button that will delete many things in the system which all have the same $job_id There is going to be 10 or so tables that need to delete * where $job_id = ? Is there a better way to do this rather than just iterating my code 10 times. I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

HI All Not sure how best to describe what i am trying to do so here goes. People use my system to order food from a menu - this is not for a restaurant where you order one starter main and desert, they will be ordering 00's of meals. The table layout is as follows: ssm_menu menu_id | menu_name | menu_price ssm_menu_connection menu_id | menu_item_id | surrogate_id ssm_menu_items menu_item_id | menu_item_name | menu_item_category ssm_menu_order job_id | menu_id | menu_item_id | menu_item_qty Menu items can appear on more than one menu. If a user orders 100 of menu_item_id 1, 2 & 3 these appear on menu_id 1& 2 Menu_id 1 only contains item_id 1, 2, 3 Menu_id 2 contains menu_item 1,2,3,4,5,6,7,8,910 when querying the database, i would like the menu_id to comeback as 1 as all of the items appear on this menu and out of all available menu items the higher percentage have been picked from this menu than menu_id 2. So on menu_id 1 = 100% of the available menu_item_id have been given a menu_item_qty but on menu_id 2, only 30% of the available menu_item_id have been selected. the sql that i have started with is the following: select menu_name from ssm_menu a inner join ssm_menu_connection b on a.menu_id = b.menu_id inner join ssm_menu_items c on b.menu_item_id = c.menu_item_id inner join ssm_menu_order d on c.menu_item_id = d.menu_item_id where job_id = 27 The result of this is: Menu One, Menu One, Menu One, Menu Two, Menu Two, Menu Two I hope this is enough information to shed some light on what i am trying to achieve and appreciate any feedback in advance. Kind Regards Adam

//DATABASE CONNECTION VARIABLES $myserver ="localhost"; $myname = "myname"; $mypassword = "mypassword"; $mydb ="mygamedb"; /*SQL CONNECTION*/ // Create connection $conn = new mysqli($myserver, $myname, $mypassword, $mydb); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { //variables $username = $_POST["username"]; $password = $_POST["password"]; $ip = $_SERVER['REMOTE_ADDR']; //INSERT USER //prepare and bind $stmt = $conn->prepare("INSERT INTO Players (Username, Password, IP) VALUES (?, ?, ?)"); //bind parameters $stmt->bind_param("sss", $username, $password, $ip); //set parameters and execute $stmt->execute(); //close $stmt->close(); //FETCH ID $resultnews = mysql_query("SELECT * FROM Players WHERE Username ='$username'"); $rownews = mysql_fetch_array($resultnews); $user_id = $rownews["ID"]; } After having suffered an SQL injection into one of my sites, I figured it was time to overhaul it and use prepared statements. I am new to this. I figured out how to an INSERT with an example, but now I need to fetch an ID and cannot get it to work. Any help much obliged. All I need is just one good example. Looked all over the place, but all I get are insert examples, which is NOT what i need. Really need one with a select and fetch.

Hi, I'm working on a reset password for WordPress and I'm stumped on this query. What the query does is when a user requests a password reset, the query makes an update to the user's password and activation key. When I run this query, it's giving me int(0) which is saying there are no rows affected. I ran a var_dump on each POST variables to check if the values are correct and it is, so I'm not sure where the error is coming from this sql statement. $resetQuery = $wpdb -> query($wpdb -> prepare("UPDATE wp_users SET user_pass = %s, user_activation_key = '' WHERE user_login = %s AND user_activation_key = %s" , $hashedPwd , $usernameemail, $key)); If anyone can please assist me, that would be great! -Halben

Okay, here is the required information: Apache/2.2.22 (Ubuntu) MySQL client version: 5.5.34 PHP extension: mysqli Here are the three raw sql statements, using sample data. Statement 1: SELECT uuid FROM EmptyCart WHERE uuid = '52b6392b-c55f-71ef-2437-c0645d3d5ea0' Statement 2: UPDATE EmptyCart SET fname="so", lname="this", company="should", address="insert", city="", state="", zip="", phone="", country="US", cart_coupon="", email="", orderSubTotal="46.15", orderTotal="46.15", numOfItems="2", items="a:2:{i:0;s:5:item1;i:1;s:5:item2;}", ids="a:2:{i:0;s:3:id1;i:1;s:3:id2;}", codes="a:2:{i:0;s:5:code1;i:1;s:5:code2;}", qtys="a:2:{i:0;s:1:1;i:1;s:1:1;}", price="a:2:{i:0;s:5:44.95;i:1;s:3:1.2;}", orderTax="0", orderShipping="0", appliedPromoIdList="", coupon="", storeId="storeid", activeShipPromotionCount="", itemImages="a:2:{i:0;s:6:image1;i:1;s:6:image2;}", date="Mon Dec 02 2013 13:40:38 GMT-0500 (Eastern Standard Time)" WHERE uuid='52b6392b-c55f-71ef-2437-c0645d3d5ea0' Statement 3: INSERT INTO EmptyCart (uuid,fname,lname,company,address,city,state,zip,phone,country,cart_coupon,email,orderSubTotal,orderTotal,numOfItems,items,ids,codes,qtys,price,orderTax,orderShipping,appliedPromoIdList,coupon,storeId,activeShipPromotionCount,itemImages,date) VALUES ("52b6392b-c55f-71ef-2437-c0645d3d5ea0","so","this","should","insert","","","","","US","","","46.15","46.15","2","a:2:{i:0;s:5:item1;i:1;s:5:item2;}","a:2:{i:0;s:3:id1;i:1;s:3:id2;}","a:2:{i:0;s:5:code1;i:1;s:5:code2;}","a:2:{i:0;s:1:1;i:1;s:1:1;}","a:2:{i:0;s:5:44.95;i:1;s:3:1.2;}","0","0","","","storeid","","a:2:{i:0;s:6:image1;i:1;s:6:image2;}","Mon Dec 02 2013 13:40:38 GMT-0500 (Eastern Standard Time)") No errors are being returned, in fact, the queries all work fine. What is happening is actually within the PHP, however I have included the SQL statements as they may be causing the logic errors in the PHP code operation. Here is the section of PHP code where the error is happening: mysqli_select_db($con,$mysql_database); $prequery = mysqli_query($con,"SELECT uuid FROM ".$mysql_table." WHERE uuid = '".$tablevalues[0]."'"); $tango = $prequery->fetch_assoc(); if ($tango["uuid"]=$tablevalues[0]) { $new_count = count($tablefields); $mysql_update = ""; for ($z=1;$z<$new_count-1;$z++){ $mysql_update .= $tablefields[$z]."=".$tablevalues[$z].", "; } $mysql_update .= $tablefields[27]."=".$tablevalues[27]; $sql = "UPDATE ".$mysql_table." SET ".$mysql_update." WHERE uuid='".$tango["uuid"]."'"; } else { $sql = "INSERT INTO {$mysql_table} ({$tablefields_implode}) VALUES ({$tablevalues_implode})"; } // pprint_r($sql); mysqli_query($con,$sql); mysqli_close($con); The Correct Functionality should be: On the client-side, a new user visits the page the form resides on and begins filling out the form. Upon initial visit, a unique identifier is assigned to the user and stored in localstorage. Every time a form field is updated/changed the value is stored in localstorage. Every 30 seconds, selected contents of the form data (as pulled from localstorage) are sent to our server database via Ajax POST. The PHP Processing file receives the POST data and performs a series of SQL injection prevention functions on the data. The PHP file constructs a new array of cleaned data and uses that data array to construct the mysqli queries above. What should happen is the unique identifier should be checked against existing database entries and if it exists the relevant entry should be updated. If the unique identifier does not exist, a new entry is made. What is currently happening: Every incoming POST is evaluated and results in an UPDATE statement, even if the unique identifier does not exist in the database. Things I have tried: I have tried changing the value of the request for $prequery to reference a variable established earlier in the processing, but the result was the same. The code selects from the database and is still somehow evaluating that nothing = something. Notes: The first SQL statement, the select one, is the reason for the change of the variable to one earlier in the array processing. The value of the variable was being displayed with double quotes around it and breaking the SQL statement, however even with the new variable that does not have the double quotes the operation is still resulting in an Update instead of Insert despite the uuid not being in the database.

I'm learning how to use PHP and prepared statements with mysqli/php and can't seem to get this code to output data. I get no error messages, just nothing output. This is the complete code, it's rather simple, created from an example I found online. <!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> <title>Untitled</title> </head> <body> <?php require_once('/includes/conn.inc.php'); $conn = dbConnect('read'); $stmt = $conn->stmt_init(); // Create a prepared statement if($stmt->prepare("SELECT 'name' FROM comments WHERE 'approved' = ?")) { // Bind your variable to replace the ? $stmt->bind_param('s', $approved); // Set your variable $approved = "Y"; // Execute query $stmt->execute(); // Bind your result columns to variables $stmt->bind_result($name); // Fetch the result of the query while($stmt->fetch()) { echo $name . ' - ' . "found"; // John Doe - Unknown... } // Close statement object $stmt->close(); } ?> </body> </html>

I have a cart function that is supposed to use the post array (with product numbers as indices and the associated value is the customer's requested quantity) and populate product information in the session by accessing the database. I would like this function to add a $key and $value pair to $prodDetail for each item in the post array. Instead, it is replacing all the $value entries with the most recently selected product data. I do not see why it is resetting all of the $value fields upon each new iteration. Please let me know if this is still unclear after you have read the following. Here is the function that I believe is the culprit: function prodArr() { if (!empty($_POST['cart'])) { global $dbType; $prodDetail = Array(); //connect to database $mysqli = Database::getInstance(); //Retrieve product data if ($dbType === 'distro') { $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'label' => &$label, 'year' => &$year, 'price' => &$price, 'qty' => &$qty); $tbl = 'products'; } elseif ($dbType === 'releases') { $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'year' => &$year, 'price' => &$price, 'qty' => &$qty); $tbl = 'products'; } elseif ($dbType === 'merch') { $results = array('img' => &$img, 'title' => &$title,'size' => &$size, 'color' => &$color, 'sex' => &$sex, 'price' => &$price, 'qty' => &$qty); $tbl = 'merch'; } $query = 'SELECT '; $query .= '`'.implode('`, `', array_keys($results)).'`'; $query .= ' FROM ' . $tbl . ' WHERE (`id` = ?) AND (`qty` > 0) AND (`agedOff` <> 1);'; foreach ($_POST['cart'] as $elKey => $element) { if (!$stmt = $mysqli->prepare($query)) { echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error; } if (!$stmt->bind_param('s', $elKey)) { echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; } if (!$stmt->execute()) { echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error; } if (!call_user_func_array(array($stmt, 'bind_result'), $results)) { echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error; } if (!$stmt->fetch()){ echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error; } $prodDetail[$elKey] = $results; printArray($prodDetail); I do not show the entire function, because I believe this is where the problem lies. Through dumping and echoing variables, I show that the $results array changes through each iteration of the foreach loop, but unfortunately it is setting all of the child arrays in $prodDetail to match the current $results array. All I want to do is add each new result set to the array. Maybe I have been staring at this for too long, but I can't seem to see the problem. Can someone please point how to fix this?

I tried to use FIND_IN_SET() with prepared statement, but did work, do not return any result, or even errors if(escape($_POST['jobCategory']) != "all-categories" && escape($_POST['countryId']) == "all-countries" && escape($_POST['careerLevel']) == "all-career-levels"): $the_array = [77,181]; $job_id_imploded = implode(',',$the_array); $query = mysqli_prepare($dbConnection,"SELECT jobs.id, jobs.job_title, jobs.country_id, employers.employer_name FROM jobs LEFT JOIN employers ON jobs.employer_id = employers.employer_id WHERE job_status = ? AND FIND_IN_SET('id',?)"); mysqli_stmt_bind_param($query,'si',$job_status,$job_id_imploded); endif; mysqli_stmt_execute($query); mysqli_stmt_bind_result($query,$job_id,$job_title,$countryId,$employer_name); while(mysqli_stmt_fetch($query)){ ?> <div class="job-title"> <a href="job_post.php?job_id=<?php echo htmlspecialchars($job_id) ?>" class="job-title-link"><?php echo htmlspecialchars($job_title); ?></a> </div> <?php } // End While ?>