PHP - Perform Query On Previous Query Data

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

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    

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

hi guys

Ive written this php to take in two variables from the http POST, the idea is that I can multiple devices submit temperature readings to the php script, the script then append the unix time stamp and then the the 3 variables - device id, temp and unix time are then stored in a mysql DB.

I can get the variables to present on a php page for debugging but I cant get the variables to be stored in the mysql DB.

See the code:

Code: [Select]
<?php

$unixtime = time();
// get device variables
$device_id=$_GET['device'];
$device_temp=$_GET['temp'];

/* 
//for testing purposes
echo "unixtime: " . $unixtime . "<br />";
echo "device id: " . $device_id . "<br />";
echo "device_temp: " . $device_temp . "<br />";
 */

// Make a MySQL Connection
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

//mysql query
$query = "INSERT INTO temperature VALUES ('',$device_id,$temp,$unixtime)";

// Insert a row of information into the relevant device table
mysql_query($query);
or die(mysql_error());  

mysql_close();
echo "Data Inserted!";


?>

I cant see where Im going wrong to correct this, but as nothing is displayed on the page i believe I am not forming the query correctly?
- any ideas would be much appreciated.

Thank you
Mathew

I'm having trouble trying to separate the output into variables that I can use/echo on my page. when I do a print I see the two rows of data all grouped together how can I separate each result base on field and row? maybe something like $line['m_id'][0], $line['m_name'][0], $line['m_id'][1], $line['m_name'][1], etc...



$bio = mysql_query("SELECT * FROM soc_meminfo 
            WHERE m_id = '".mysql_real_escape_string($en['mm_id'])."'");
                             
if (mysql_num_rows($bio) == 0) call404();
while ($line = mysql_fetch_assoc($bio)) {
foreach ($line as $key => $value) {
        $en['b'.$key] = str_replace("\n",'<br/>',stripslashes($value));
        }
echo '<pre>';
    print_r($line);
echo '</pre>';
}

Alright, I've spent over a week trying to fix this now - And Im getting frustrated! I asked at other forums, I asked co-workers and I asked friends-of-friends, and nobody can explain what happens. Let's take a look at this first:

$name = mysql_real_escape_string($_POST['name']);
mysql_query(sprintf("UPDATE em_users SET name='%s' WHERE id='" . $in_user['id'] . "'", $name));

This will insert NO data on the Name field in the database. Obviously, I thought the $_POST variable wasn't passed correctly, but echo'ing it just before the query WILL show data. And as I said, I tried everything possible for the last week. Switching variables, adding static text on the $name variable instead of using the $_POST content (this does work). I used very very simple test data on the form, such as my name "Mark" or "test" and "hey". The query is correctly executed everytime.

The truely WEIRD thing is, if I ensure there is content in $name before executing the query it will work as expected everytime. Like this:

$name = mysql_real_escape_string($_POST['name']);
$name && mysql_query(sprintf("UPDATE em_users SET name='%s' WHERE id='" . $in_user['id'] . "'", $name));

Of course I could do this, but I want to know why my code does or doesn't work + it's a lot of work to do for something that worked fine a week ago. It has spread to a lot of forms on my website that $_POST variables aren't processed correctly - and it happened out of nowhere. Even on codes that havnt changed in months.

I really need help on fixing this! This project has been in development for nearly two years, and without a fix it's pretty much lost

I've only been studying PHP for a week now and have come across this problem which I'm sure there is a simple answer to, but I just can't figure it out and would appreciate some help. I've spent far too long on this minor issue already!

I have a table which contains a list of products, in this case books, which stores the date when each new item is added.
I have a query that then searches through this table and extracts the 6 most recent additions.

Here is the code I have so far:

Code: [Select]
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 6");
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
while($row = mysql_fetch_array($sql)){
             $id = $row["id"];
$title = $row["title"];
$author = $row["author"];
$price = $row["price"];
$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
$dynamiclist .= //My table showing the products
}
} else {
$dynamicList = "There are currently no Books listed in this store";
}
This works well when I need to display the 5 most recent products in a normal table fashion, one below another, on the page.

However, I want to display the products in a more personalised order. For example display the newest item in one section of the page and 3rd newest in another.

What is the best way to select an individual row from a query? To extract the details of the 2nd newest item to display in the header, for example.

Thanks for your help.

I know that I can fairly easily pull my data from the database and view it in a browser.
I can also 'polish' it with some HTML or put it into a table.
Can I get an item from a given VARIABLE to appear inside of an INPUT box, so that it looks the same as when it was initially submitted?
Can it be done with a multiple choice SELECT dropdown, so that the item chosen is viewable again?

I am at a loss why my query is not inserting values into db. Even if I do echo $query or var_dump($query) there is nothing printed at all. All values are being passed successfully just not being inserted. I am getting 'Could not connect' but I do not know why. All connections are established and as a test I took this code and ran it on it's own with dummy data and it inserted the data fine. I can only think it has something to do with the $response_array.  Where am I going wrong. and would appreciate any help. Thanks

Code: [Select]
<?php require_once('Connections/sample.php'); ?>
<?php

session_start();

$new = 1;
$activity = 'General Contact Enquiry';
$mobile = 'Submitted from mobile';
$name = mysql_real_escape_string($_POST['GC_name']);
$department = mysql_real_escape_string($_POST['GC_department']);
$message = mysql_real_escape_string($_POST['GC_message']);
$email = mysql_real_escape_string($_POST['GC_email']);
$company = mysql_real_escape_string($_POST['GC_company']);
$position = mysql_real_escape_string($_POST['GC_position']);

//response array with status code and message
$response_array = array();

//validate the post form

//check the name field
if(empty($name)){

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'Name cannot be blank';

//check the name field
} elseif(empty($company)) {

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'You must enter a company name';

//check the position field
}elseif(empty($position)) {

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'You must enter a position';

//check the email field
} elseif(empty($email)) {

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'You must enter a valid email address';

//check the dept field
}elseif($department=="Choose Department") {

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'You must select a department';

//check the message field
}elseif(empty($message)) {

    //set the response
    $response_array['status'] = 'error';
    $response_array['message'] = 'You must enter a message';

//check the dept field
}
else {

    //set the response
    $response_array['status'] = 'success';
    $response_array['message'] = 'Your enquiry has been sent succesfully';
    $flag=1;
 }

//send the response back
echo json_encode($response_array);

if($flag == 1) {

    mysql_select_db($database_sample, $sample);
    $query = 'INSERT INTO feedback (company, department, name,  email, position, feedback, date, new) VALUES (\''.$company.'\', \''.$department.'\', \''.$name.'\', \''.$email.'\', \''.$position.'\', \''.$message.'\', NOW() , \''.$new.'\')';
    mysql_query($query) or die("Could not connect");
    }
    

?>

Hello,

I hope all of you are safe with your families.

Currently I am starting with PHP Coding and I am trying to do a simple query to MySQL DB using PHP but even when is able to bring the number of rows is not displaying the values in the DB.

This is my code:

<?php
$servername = "localhost";
$database = "mydbtest";
$username = "root";
$password = "root";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
//echo "Connected successfully";
$myquery = "SELECT * FROM Country";
$result = $conn->query($myquery);
$numf = $result->num_rows;
echo "Number of rows " . $numf . "<br>";
if($numf >0){
	while($row = $result->fetch_object()){
		echo "Code" . $row->countrycode . "<br>";
		echo "Country" . $row->countryname . "<br>";
	}
 
	}else{
		echo '0 results';
	}

mysql_free_result($myout);
mysqli_close($conn);
?>

What is failing?

 

Thanks in advance for the assistance.

I have a text box that I use to post comments and save them to my database. I can go to the database and the data I entered looks perfect but when I pull it out it all runs together.

 

This is how I entered it and the way it is in the database:

I have a text box that I use to post comments and save them to my database.
I can go to the database, the data I entered looks perfect but when I pull it out and display it it all runs together.
No new lines just all one paragraph.
Can someone help?

This is how it came out:

I have a text box that I use to post comments and save them to my database. I can go to the database, the data I entered looks perfect but when I pull it out and display it it all runs together. No new lines just all one paragraph. Can someone help?

I got it indenting the paragraphs, but I get all the data back I entered all in 1 paragraph, is there something I am missing to be able to recognize the new lines??

Hi,

I want to develop array like following

Code: [Select]
$BCD=array('type' =>'TYPE1',
    array(
        0=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0),
1=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0),
2=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0),
3=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0),
        )
    ));   
   I have written following code to achieve the same but not getting result.
   
  Code: [Select]
  $sql = "select * from tablename ";
    $result = mysql_query($sql);
    $k=0;
    while ($row = $db->mysql_fetch_array($result)) {
           // array_push($BCD['type'],$row['type']);
           
            $BCD1=array('type' =>$row['type'],
            array(
                $k=>array('column1'=>$row['column1'],'column2'=>$row['column2'],'column3'=>$row['column3'],'column4'=>$row['column4'],
'column5'=>$row['column5'],'column6'=>$row['column6'],'column7'=>$row['column7'])
            ));
            $k++;
    }

Hi.
I am trying to get a PHP Query to refresh every 10 seconds. I have scoured the internet for days and could not find anything of much use. Plenty of Ajax going on (whatever that is) but the scripts were immense. I only want to refresh 7 lines of PHP Query Code.

Can you please tell me if this is possible?

Thanks in Advance.

Hello, I seem to have some problem with my script that has a goal of outputting data about the file size when a filename is queried.   The sql table name is file The table columns are as followed:  id | name | mime | size   The file name is stored in name. The script that i have that gets the file name is:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
WHILE ($rows = mysql_fetch_array($query)):
   $File_name = $rows['name'];  
  
echo "<option value=$File_name>$File_name/option> <br>";
endwhile;
?>
</datalist>

<input type="submit" class="button" >
</form>
</body>
</html>

 

Hi guys, im trying to connect to a database and get the value for the user in the row called 'user_credit', if it equals 1 or more then i want to show the ''You have &pound; ....'' bit in the script.

Problem is nothing shows at all, even without the if statement. I have changed the value for me in the database so in user_credit the value is 100, which is more than 1 so it should appear. I have probably done something wrong. Any ideas?

Code: [Select]
<?
include '../admin/database/membership_dbc.php';

$r = mysql_query("SELECT * FROM users WHERE user_name='".safe($_SESSION['user_name'])."'") or die ("Cannot find table");
while( $cred = mysql_fetch_array($r) ) {
if ($cred >= '1' )
{
?>
  <p>You have &pound;<? echo $cred['user_credit']; ?> available on you account,
    would you like to use it on this order?<br>
    <label for="credit"></label>
    <select name="credit" id="credit">
      <option value="Y" selected>Yes, use credit</option>
      <option value="N">No, save credit</option>
    </select>
  </p>
  <?
}
}
  ?>