PHP - Syntax For Picking Mysql Column From Form

Hi guys   I have this code below and all works fine when submitting this online application apart from when someone types either ' # & into one of the comment fields in which it throws up the error. Have tried various fixes from across the internet but no joy. Can anyone offer suggestions?   <?php $con = mysql_connect("localhost:3306","root","password"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } mysql_select_db('sfapp', $con); $sql="INSERT INTO 'sfapp' ('surname_add','forename_add','dob_add','hometele_add','mobiletele_add','homeadd_add','siblings_add','schoolname_add','headname_add','schooladd_add','schooltele_add','schoolem_add','alevel_add','personstate_add','nameprovided_add','pe_add','se_add','PredGrade_Art','PredGrade_AScience','PredGrade_BusStudies','PredGrade_Electronics','PredGrade_EnglishLang','PredGrade_EnglishLit','PredGrade_French','PredGrade_German','PredGrade_Geog','PredGrade_Graphics','PredGrade_History','PredGrade_Maths','PredGrade_SepScience','PredGrade_ProductDesign','PredGrade_Spanish','PredGrade_Other','Gender_Male','Gender_Female','Sub_EnglishLit','Sub_Maths','Sub_FurtherMaths','Sub_Biology','Sub_Chemistry','Sub_Physics','Sub_French','Sub_German','Sub_Spanish','Sub_Geography','Sub_History','Sub_RE','Sub_FineArt','Sub_Business','Sub_Computing','Sub_GlobPersp','Sub_DramaAndTheatre','Sub_PE','Sub_Dance','Sub_Politics','Sub_Psychology','Sub_Sociology','readprospect_chk','Sib_Yes','Sib_No','Current_Student_Yes','Current_Student_No','I_Understand_chk','Current_Education_chk','Local_Care_chk','Staff_Cwhls_chk','Sub_Film')   VALUES ('$_POST[surname_add]','$_POST[forename_add]','$_POST[dob_add]','$_POST[hometele_add]','$_POST[mobiletele_add]','$_POST[homeadd_add]','$_POST[siblings_add]','$_POST[schoolname_add]','$_POST[headname_add]','$_POST[schooladd_add]','$_POST[schooltele_add]','$_POST[schoolem_add]','$_POST[alevel_add]','$_POST[personstate_add]','$_POST[nameprovided_add]','$_POST[pe_add]','$_POST[se_add]','$_POST[PredGrade_Art]','$_POST[PredGrade_AScience]','$_POST[PredGrade_BusStudies]','$_POST[PredGrade_Electronics]','$_POST[PredGrade_EnglishLang]','$_POST[PredGrade_EnglishLit]','$_POST[PredGrade_French]','$_POST[PredGrade_German]','$_POST[PredGrade_Geog]','$_POST[PredGrade_Graphics]','$_POST[PredGrade_History]','$_POST[PredGrade_Maths]','$_POST[PredGrade_SepScience]','$_POST[PredGrade_ProductDesign]','$_POST[PredGrade_Spanish]','$_POST[PredGrade_Other]','$_POST[Gender_Male]','$_POST[Gender_Female]','$_POST[Sub_EnglishLit]','$_POST[Sub_Maths]','$_POST[Sub_FurtherMaths]','$_POST[Sub_Biology]','$_POST[Sub_Chemistry]','$_POST[Sub_Physics]','$_POST[Sub_French]','$_POST[Sub_German]','$_POST[Sub_Spanish]','$_POST[Sub_Geography]','$_POST[Sub_History]','$_POST[Sub_RE]','$_POST[Sub_FineArt]','$_POST[Sub_Business]','$_POST[Sub_Computing]','$_POST[Sub_GlobPersp]','$_POST[Sub_DramaAndTheatre]','$_POST[Sub_PE]','$_POST[Sub_Dance]','$_POST[Sub_Politics]','$_POST[Sub_Psychology]','$_POST[Sub_Sociology]','$_POST[readprospect_chk]','$_POST[Sib_Yes]','$_POST[Sib_No]','$_POST[Current_Student_Yes]','$_POST[Current_Student_No]','$_POST[I_Understand_chk]','$_POST[Current_Education_chk]','$_POST[Local_Care_chk]','$_POST[Staff_Cwhls_chk]','$_POST[Sub_Film]')"; if (!mysql_query($sql,$con))   {   die('Error: ' . mysql_error());   }   ?> <?php //if "email" variable is filled out, send email   if (isset($_REQUEST['pe_add']))  {     //Email information     $admin_email = $_REQUEST['pe_add'];   $forename = $_REQUEST['forename_add'];   $email = "autoreply@testing.com";   $subject = "Application";   $desc =   "Dear $forename   Thank you for submitting your online application, we will be in touch shortly. " ;       //send email   mail($admin_email, "$subject", "$desc", "From:" . $email);     //Email response   echo "Thank you for contacting us!";   }     //if "email" variable is not filled out, display the form   else  { ?> If you are seeing this, you need to go back and fill out the Personal Email section! <?php   }   header("location:complete.php"); mysql_close($con) ?>     Thanks in advance.

Hi..

I create mysql syntax for query testing before i input to my php code

here is my mysql code:

Code: [Select]
set @t = 0;
set @rqty=31968;
SELECT LOT_CODE as code,  DATE_ENTRY,   
CASE WHEN @t+OUTPUT_QTY > @rqty
THEN @rqty  -@t 
ELSE OUTPUT_QTY
END as qty,
@t := @t + d.OUTPUT_QTY as cumulative   
FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' AND (@t < @rqty);

and i attach the sample output of the above query.

Now that query test is work i  will input that code to my php codes.


     $sql = "SELECT SKUCode, Materials, Comp, Qty 
     FROM bom  WHERE SKUCode = '$SKUCode'";
     $res = mysql_query($sql, $con);

    ($row = mysql_fetch_assoc($res));
     $Materials = $row['Materials'];
     $Qty = $row['Qty'];
     $Comp = $row['Comp']; //P28
//-----Compute Req Qty and Save to table---//     

     $ReqQty = $Qty * $POReq; // 31968


     
$sql = "UPDATE bom SET ReqQty = '$ReqQty' WHERE SKUCode = '$SKUCode' AND Materials = '$Materials'";
$resReqQty = mysql_query($sql, $con);

$t = 0;

$sql = "SELECT LOT_CODE as code,  DATE_ENTRY,   
CASE WHEN $t+OUTPUT_QTY > $ReqQty 
THEN $ReqQty  -$t  
ELSE OUTPUT_QTY 
END as qty,
$t := $t + d.OUTPUT_QTY as cumulative   
FROM dipping d  WHERE SUBSTR(LOT_CODE, 9,4) = '$Comp' AND ($t < $ReqQty)";



when I echo the query:

I got this:

 SELECT LOT_CODE as code, DATE_ENTRY, CASE WHEN 0+OUTPUT_QTY > 31968 THEN 31968 -0 ELSE OUTPUT_QTY END as qty, 0 := 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' AND (0 < 31968)

then I run it to the sql

and I got an error:

Error Code : 1064
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 ':= 0 + d.OUTPUT_QTY as cumulative FROM dipping d WHERE SUBSTR(LOT_CODE, 9,4) = '' at line 1
(0 ms taken)


Any help is highly appreciated

Thank you so much

My Php Buddies,

I have mysql tbl columns these:

id: 
date_&_time: 
account_activation_code: 
account_activation_status: 
id_video_verification_status: 
id_verification_video_file_url: 
username: 
password: 
primary_domain: 
primary_website_email: 
sponsor_username: 
 

Now, I want to display their row data by excluded a few columns.

Want to exclude these columns:

date_&_time

account_activation_code

account_activation_status

id_verification_video_file_url

password

 

So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:

Id: 1
Id Video Verification Status: 1
Username: requinix
Primary_domain: requinix.majesty
Primary Website Email: requeen@requinix.majesty
Sponsor Username: phpsane
Recruits Number: 2

 

For your convenience only PART 1 works. Need help on Part 2

My attempted code:

 

	PART 1
	<?php 
	// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
	// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
	while($row = $query->fetch_assoc()){
    $result[] = $row;
}
	// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
	foreach ($columnArr as $value) { 
echo "<b>$value</b>: ";?><br><?php 
} 
	?>
	 
	PART 2
	<?php 
	//Display User Account Details 
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> 
<?php 
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); 
foreach ($excluded_columns as $value2) 
{    
    echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value) 
{ 
    
    if($value != "$value2")
    { 
        $label = str_replace("_"," ","$value"); 
        $label = ucwords("$label"); 
        //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php 
        echo "<b>$label</b>: "; echo "${$value}";?><br><?php 
    }
} 
?>
	

PROBLEM: Columns from the excluded list still get displayed.

Edited November 19, 2018 by phpsane

i have created a table named 'masteruseraccounts' in mysql.it has fields : key(auto increment,int);name(varchar(50));idNo(varchar(12));password(varchar(50));mobileNo(varchar(10));email(varchar(50)).
i entered one row as follows:
key |     name            |       idNo            |   password    | mobileNo      |   email
1    |  nikhil upadhyay | 2009AAPS092H  | nikhil            |9505042041  |  aliasnikhil@gmail.com

and the idNo is the login name and is stored in a session variable($_SESSION['MM_Username']).i confirmed that the idNo is properly getting stored in session variable by using echo statement.i am also using the session_start().depending on this idNo i was trying to display the rest of information.so i tried to use the WHERE clause but in vain
here is my syntax
Code: [Select]
<?php
session_start();
mysql_select_db("mydb");
$userId = mysql_real_escape_string($_SESSION['MM_Username']);

$query = "SELECT * " .
"FROM masteruseraccounts".
"WHERE idNo = '$userId' ";


$results = mysql_query($query)
or die(mysql_error());

while ($row = mysql_fetch_array($results)) {
extract($row);
echo $idNo;
echo " - ";
echo $email;
echo " - ";
echo $mobileNo;
echo "<br>";

}

?>

it says "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 '= '2009AAPS092H'' at line 1"

instead if i do not use the where clause then it displays all info correctly.i even tried removing the single quotes around $userId.also tried the WHERE clause as( "WHERE idNo = " .$userId. ""; ) still it gives error.

i even tried hardcoding it with the WHERE statement as WHERE idNo = 2009AAPS092H but it doesnt work.putting quotes around 2009AAPS092H also doesnt help.

please i am pulling my hair out.someone help me.i thought there might be some different syntax for varchar types but i tried hardcoding the WHERE clause as (WHERE key = 1) this also gives error "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 'key = 1' at line 1" now this key field being int type i know it doesnt require quotes around it.

any help is very much appreciated.

Hey guys, I have a url that looks like this

http://www.mysite.com/our-company-t-2.html

I need to extract the number at the end. It's 2 here but could be 2478 for example. Just wondering how I can do this. Would it need to do something like look for anything between "-" and ".html" ?

Using phpMyAdmin I loaded 6 test records with the id set to auto_increment and it loaded all the data correctly with id # 1-6.

Then from somewhere it got the number 333353  and auto_increments it as the value for the id. So now I have id's 1-6 and 333353, 333354, ect. For every record I add it increments it.

I deleted all but records 1-6 and tried again but it has the last value of 3333xx stored somewhere and increments it. Deleted them again, closed the program, came back and it still does it.

Ok I solved the problem,

this was my initial query which didn't work:


$query3 = "INSERT INTO user (avatar) VALUES ('$avatar') WHERE user_id = '$dbuser_id'";


but this one works:


"UPDATE user set avatar = '$avatar' WHERE user_id = '$dbuser_id'";

Howdy folks,

I am creating a Facebook app for a bit of fun and practice and getting the following error in index.php:

Code: [Select]
Invalid query -- SELECT * FROM `results` WHERE `resultLow` <= AND `resultHigh`>= -- 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 'AND `resultHigh`>=' at line 1
Here is the area:

Code: [Select]
$res = query("SELECT * FROM `results` WHERE `resultLow` <=$user_score AND `resultHigh`>=$user_score");
Any help would be appreciated.

Hi guys i have the following code which is misbehaving can anyone see where its wrong?

Code: [Select]
<?php 
include 'dbc.php';


page_protect();
company();

   
$Referrer = mysql_query("SELECT * FROM Referrer WHERE SentOut='0' ");



if (isset($_POST['submit'])) {
//Assign each array to a variable
$StaffMember = $_POST['StaffMember'];
$referrer = $_POST['referrer'];
$referred = $_POST['referred'];
$SentOut = $_POST['SentOut'];
$today = date("y.m.d H:i:s");
$user_id = $_SESSION['user_id'];
$IssueNum = $_POST['Referrerid'];
$limit = count($StaffMember);


for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";
$values[$k] = array(
$StaffMember[$k],$referrer[$k],$referred[$k],$SentOut[$k],$today,$user_id
); // build the array of values for the query string
}

foreach( $values as $key => $value ) {
$query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, 
SentOutDate, SentOutBy) 
VALUES ('" . implode( '\', \'', $value ) . "')
WHERE IssueNum= '{$IssueNum[$key]}'";
mysql_query($query) or die(mysql_error());
  
}





}



if (checkAdmin()) {





?>



           
<html>
<head>
<title>Book Off Holiday</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script src="php_calendar/scripts.js" type="text/javascript"></script>

<link href="styles.css" rel="stylesheet" type="text/css">
</head>

<body>



<form name="form" action="SendReferrers.php" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr>
    <td colspan="3">&nbsp;</td>
  </tr>
 
 
   
   
    <td width="160" valign="top">
<?php 
if (isset($_SESSION['user_id'])) {
}


?>
<a href="admin.php">Admin CP </a>


</td>


 
    <td width="732" valign="top">

<p>
<h3 class="titlehdr">New KPI</h3> 




  <table width="300px" border="0" align="Centre" cellpadding="2" cellspacing="0">
          <tr bgcolor="#000050">
           
         
<td width="20px"><h3 class="Text2">Referrer ID</h3></td>
            <td width="20px"><h3 class="Text2">Staff Member</h3></td>
<td width="20px"><h3 class="Text2">referrer</h3></td>
<td width="20px"><h3 class="Text2">referred</h3></td>
<td width="40px"><h3 class="Text2">Sent Out</h3></td>



                   </tr>
         
          <?php while ($rrows = mysql_fetch_array($Referrer)) {?>
          <tr>
         
 
  <td><h3 class="Text3"><input type="" name="Referrerid[]" id="Referrerid[]" size="4" value="<?php echo $rrows['IssueNum'];?>" /></h3></td>
<td><h3 class="Text3"><input type="" name="StaffMember[]" id="StaffMember[]" size="4" value="<?php echo $rrows['StaffMember'];?>" /></h3></td>
<td><h3 class="Text3"><input type="" name="referrer[]" id="referrer[]" value="<?php echo $rrows['referer'];?>" /></h3></td>
<td><h3 class="Text3"><input type="" name="referred[]" id="referred[]" value="<?php echo $rrows['referred'];?>" /></h3></td>
<td><h3 class="Text3"><input name="SentOut[]" type="checkbox" value="1" id="SentOut[]"></h3></td>




                 
          </tr>
       
          <?php } ?>
  </table>
<input name="submit" type="submit" id="submit" value="Create">



</table>
</form>
</body>
</html>
<?php }
?>
the error i get when the submit button is clicked is

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 '(StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES' at line 1

any help would be appriciative

I have 3 staff members that need to pick vacation in a certain order.
Each have X weeks vacation and can pick off a calendar their choice 1-3 weeks  per round.
I'm trying to loop through the DB to find who is next to pick with weeks left

~~~~~~First round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  3 weeks available
STAFF 2 - PICK ORDER 2 -  5 weeks available
STAFF 3 - PICK ORDER 3 -  3 weeks available

Staff 1 takes 2 Weeks
Staff 2 takes 1 Weeks
Staff 3 takes 3 Weeks

~~~~~~Second round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  1 weeks available
STAFF 2 - PICK ORDER 2 -  4 weeks available
STAFF 3 - PICK ORDER 3 -  No weeks left

Staff 1 takes 1 Weeks
Staff 2 takes 3 Weeks
Staff 3 Skipped

~~~~~~Third round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  No weeks left
STAFF 2 - PICK ORDER 2 -  1 weeks available
STAFF 3 - PICK ORDER 3 -  No weeks left

Staff 1 Skipped
Staff 2 takes 1 Weeks
Staff 3 Skipped

~~~~~~~~~~~~
All staff 0 weeks left end

--calendar.php--
$year=2020;
$sql = "SELECT * FROM vac_admin WHERE pick_year='$year';
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
$row_admin = mysqli_fetch_assoc($result);
}
$current_pick_staff = $row_admin['current_pick_staff'];

$sql = "SELECT *  FROM vac_pick_order WHERE  pick_year='$year'  &&  pick_order = '$current_pick_staff'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
if($row['vac_c_counter'] < 0){
$emp_num = $row['emp_num'];
}ELSE{
??????????????????
goto next staff with weeks > 0
?????Somthing like
if ($current_pick_staff == 3){
	$current_pick_staff = 1;
}ELSE{
	$current_pick_staff++;
}
??????????????????  
}

~<FORM>~~~~~~~~~~~~~~~~~~~~~
Staff with $emp_num can now pick ~~~~~~ $_POST -> $date = XXXX-XX-XX; $num_weeks = X; $emp_num;
~</FORM>~~~~~~~~~~~~~~~~~~~~~

--process.php--
$year = 2020;
$date = $_POST['date'];
$num_weeks = $_POST['num_weeks'];
$emp_num = $_POST['emp_num'];

$sql = "INSERT INTO vac_picks (pick_year,emp_num,date) VALUES ($year,$emp_num,$date)";
$sql = "UPDATE vac_pick_order SET vac_c_counter=vac_c_counter - $num_weeks WHERE emp_num='$emp_num';
$sql = "UPDATE vac_admin SET pick_order=pick_order +1 WHERE pick_year='$year' ;

Then back to calendar.php until all weeks gone.

 

 

 

 

I have a mysql table named songs with 3 columns, id, artist,title.

Most of the songs in the artist column are correct, ex:

artist
------------------
John Denver
Loretta Lynn
Shania Twain
Luke Bryan

But some of the songs in the artist column are reveresed with a comma, ex:

artist
-----------------
Dever, John
Lynn. Loretta
Twain, Shania
Bryan, Luke

Is there an easy php code snippet or mysql statement that i can use to reverse the order of first name and last name and remove the comma in the last example so the artst columd matches the first example?

I hope this makes sense, thanks, Dale.

Php Buddies,

Just how do you insert an uploaded img via html for ($_POST) to mysql tbl and what type of column should it be ?

Also, how to link the img from the mysql column to a webpage ?

Following html links the img from a folder/directory to a webpage:

<a href="../html-link.htm"><img src="flower.jpg" style="width:82px; height:86px" title="White flower" alt="Flower"></a>

Is there a way to use php to make an automatic column addition to a table?  like if I had a comment form and want to save the replys in a database, I would want a column for each reply, is there a way to make a new column each time someone submits the form?

Hey all,

I keep getting this error:

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 '' at line 2

When I use the script below. I'm finding it a bit confusing because everything about it continues to work, it's just it gives me an error. When the script runs, the outcome is

"Success! Your dog now looks far more energetic!
Looks like this food is all used up."

Followed by the error, which cuts the remainder of the page off. Does anybody know why it's doing this?

$dogyay = $_POST['dogid'];

$checkenergy = "SELECT energy FROM dogs WHERE id=$dogyay";   
$energylevel = mysql_query($checkenergy) or die(mysql_error());
$row = mysql_fetch_array($energylevel) or die(mysql_error());

if($row['energy'] >= 100)
{ echo "<b>Oops!</b> Looks like your dog is full right now...";}

else{ 

echo "<b>Success! Your dog now looks far more energetic!</b><br><br>";

$sql11="UPDATE dogs SET energy=energy + 50 WHERE id=$dogyay";
$result11=mysql_query($sql11);

$sql12="UPDATE items SET uses = uses - 1 WHERE itemid=$id";
$result12=mysql_query($sql12);

$checkuses = "SELECT uses FROM items WHERE itemid=$id";   
$useslevel = mysql_query($checkuses) or die(mysql_error());
$row = mysql_fetch_array($useslevel) or die(mysql_error());

if($row['uses'] == 0)
{ echo "Looks like this food is all used up.<bR><br>";

mysql_query("DELETE FROM items WHERE itemid='$id'") 
or die(mysql_error());}


Thanks !

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=352460.0