[RESOLVED] Weird thigs. Values did not inserted into the database.

kingdm

Hello everyone.

I have a question. I'm using this code to insert my values into the database. I never had a problem on it until a while ago when I created my Edit module of licenses page in my project.

Here is snippet of the form:

licenses.php

<form method="post" action="edit_licenses_verify.php">
		<label>Phil. License (PRC)
		<span class="small">license number</span>
		</label>
		<input type="text" name="prc_number" maxlength="50" value="<?php echo $_SESSION['prc_number']; ?>" />

	<label>Rank
	<span class="small">rank in PRC license</span>
	</label>
	<input type="text" name="prc_rank" maxlength="50" value="<?php echo $_SESSION['prc_rank']; ?>" />
</form>

The $_SESSION in the value attribute is already fetch upon the login. There is no problem with that because it displays the current value for prc_number and prc_rank of the user. (because this is an edit module)

edit_licenses_verify.php

$con = mysql_connect("localhost","","") or die(mysql_error());
$db = mysql_select_db("mytable_project_dbase",$con);

$_SESSION['prc_number'] = cleanString($_POST['prc_number']);
$_SESSION['prc_rank'] = cleanString($_POST['prc_rank']);

$db_prcnumber = $_SESSION['prc_number'];
$db_prcrank = $_SESSION['prc_rank'];
.
.
.
// then some other codes and validations
.
.
.
if(count($errors) > 0)
{
    //displays errors
}

else
{
    //update database if no error, this is the part where I think must be the problem

   $sql_change = "UPDATE users SET `prc_number`='$db_prcnumber,`prc_rank`='$db_prcrank',`prc_mbday`='$db_prcmbday',`prc_dbday`='$db_prcdbday',`prc_ybday`='$db_prcybday',`marina_number`='$db_marinanumber',`marina_rank`='$db_marinarank',`marina_mbday`='$db_marinambday',`marina_dbday`='$db_marinadbday',`marina_ybday`='$db_marinaybday',`sirb_number`='$db_sirbnumber',`sirb_mbday`='$db_sirbmbday',`sirb_dbday`='$db_sirbdbday',`sirb_ybday`='$db_sirbdybday',`sirc_number`='$db_sircnumber',`sirc_rank`='$db_sircrank' WHERE id='".$_SESSION['id']."'";

   $result=mysql_query($sql_change);
}

I pasted my whole UPDATE query, that is the table on the database and the values. My problem is I always used this since the beggining, but why now it won't insert into the database anymore. the prc_number and prc_rank in the table value won't change based on the input I placed on the form.

Can someone help me regarding this? Hoping for your kind assist. Thank you.

halojoy

Could it be you
are missing session_start() ?
This has to be executed before any use of $_SESSION variables

kingdm

Thanks for your quick reply halojoy. I did have an session_start(); right after the start of the page after the <?php.

Do you think what could be the problem? I've been using this query for my entire project, but now just a while ago, it didn't work, 🙁

halojoy

If you have session_start() in beginning of pages:
- licenses.php
- edit_licenses_verify.php
.. then this is not the problem.
session_start() must be there.

kingdm

Thanks for your time in assisting me halojoy. I did figure it out. I placed die(mysql_error()); and the browser tells me that I have an error in my SQL query line 1. Upon checking it thoroughly, I see the fault, a missing ' after the '$db_prcnumber .

Thanks a lot for your time, once again. By the way, could you suggest me on how to make a much better way of writing the query situated like mine? Because of this kind of missing ' mistake, I thought this is not a better way to write it since I did miss to see it.

laserlight

kingdm wrote:
By the way, could you suggest me on how to make a much better way of writing the query situated like mine? Because of this kind of missing ' mistake, I thought this is not a better way to write it since I did miss to see it.

Switch to the PDO extension or MySQLi extension and use prepared statements.

kingdm

Thanks for your reply silverlight. I did not have an idea regarding those new terms for me, which learns PHP in no more than a month. Can you show me a little sample of it relatively to my query posted above?

Thanks.

laserlight

Given that $db is the PDO database connection object:

$sql = "UPDATE users
    SET `prc_number`=:prcnumber, `prc_rank`=:prcrank, `prc_mbday`=:prcmbday,
        `prc_dbday`=:prcdbday, `prc_ybday`=:prcybday, `marina_number`=:marinanumber,
        `marina_rank`=:marinarank, `marina_mbday`=:marinambday, `marina_dbday`=:marinadbday,
        `marina_ybday`=:marinaybday, `sirb_number`=:sirbnumber, `sirb_mbday`=:sirbmbday,
        `sirb_dbday`=:sirbdbday, `sirb_ybday`=:sirbdybday, `sirc_number`=:sircnumber,
        `sirc_rank`=:sircrank
    WHERE id=:id";
$stmt = $db->prepare($sql);

$stmt->bindValue(':prcnumber', $db_prcnumber, PDO::PARAM_STR);
$stmt->bindValue(':prcrank', $db_prcrank, PDO::PARAM_STR);
$stmt->bindValue(':prcmbday', $db_prcmbday, PDO::PARAM_STR);
$stmt->bindValue(':prcdbday', $db_prcdbday, PDO::PARAM_STR);
$stmt->bindValue(':prcybday', $db_prcybday, PDO::PARAM_STR);
$stmt->bindValue(':marinanumber', $db_marinanumber, PDO::PARAM_STR);
$stmt->bindValue(':marinarank', $db_marinarank, PDO::PARAM_STR);
$stmt->bindValue(':marinambday', $db_marinambday, PDO::PARAM_STR);
$stmt->bindValue(':marinadbday', $db_marinadbday, PDO::PARAM_STR);
$stmt->bindValue(':marinaybday', $db_marinaybday, PDO::PARAM_STR);
$stmt->bindValue(':sirbnumber', $db_sirbnumber, PDO::PARAM_STR);
$stmt->bindValue(':sirbmbday', $db_sirbmbday, PDO::PARAM_STR);
$stmt->bindValue(':sirbdbday', $db_sirbdbday, PDO::PARAM_STR);
$stmt->bindValue(':sirbdybday', $db_sirbdybday, PDO::PARAM_STR);
$stmt->bindValue(':sircnumber', $db_sircnumber, PDO::PARAM_STR);
$stmt->bindValue(':sircrank', $db_sircrank, PDO::PARAM_STR);
$stmt->bindValue(':id', $_SESSION['id'], PDO::PARAM_STR);

$stmt->execute();

kingdm

Thanks for that silverlight. It is very neat and ideal to read for a lengthy query.

$sql = "UPDATE users
    SET `prc_number`=:prcnumber, `prc_rank`=:prcrank, `prc_mbday`=:prcmbday,
        `prc_dbday`=:prcdbday, `prc_ybday`=:prcybday, `marina_number`=:marinanumber,
        `marina_rank`=:marinarank, `marina_mbday`=:marinambday, `marina_dbday`=:marinadbday,
        `marina_ybday`=:marinaybday, `sirb_number`=:sirbnumber, `sirb_mbday`=:sirbmbday,
        `sirb_dbday`=:sirbdbday, `sirb_ybday`=:sirbdybday, `sirc_number`=:sircnumber,
        `sirc_rank`=:sircrank
    WHERE id=:id";

Out of curiosity, can I use this alone in replace for my above query?

$stmt = $db->prepare($sql);

$stmt->bindValue(':prcnumber', $db_prcnumber, PDO::PARAM_STR);
$stmt->bindValue(':prcrank', $db_prcrank, PDO::PARAM_STR);
$stmt->bindValue(':prcmbday', $db_prcmbday, PDO::PARAM_STR);
$stmt->bindValue(':prcdbday', $db_prcdbday, PDO::PARAM_STR);
$stmt->bindValue(':prcybday', $db_prcybday, PDO::PARAM_STR);
$stmt->bindValue(':marinanumber', $db_marinanumber, PDO::PARAM_STR);
$stmt->bindValue(':marinarank', $db_marinarank, PDO::PARAM_STR);
$stmt->bindValue(':marinambday', $db_marinambday, PDO::PARAM_STR);
$stmt->bindValue(':marinadbday', $db_marinadbday, PDO::PARAM_STR);
$stmt->bindValue(':marinaybday', $db_marinaybday, PDO::PARAM_STR);
$stmt->bindValue(':sirbnumber', $db_sirbnumber, PDO::PARAM_STR);
$stmt->bindValue(':sirbmbday', $db_sirbmbday, PDO::PARAM_STR);
$stmt->bindValue(':sirbdbday', $db_sirbdbday, PDO::PARAM_STR);
$stmt->bindValue(':sirbdybday', $db_sirbdybday, PDO::PARAM_STR);
$stmt->bindValue(':sircnumber', $db_sircnumber, PDO::PARAM_STR);
$stmt->bindValue(':sircrank', $db_sircrank, PDO::PARAM_STR);
$stmt->bindValue(':id', $_SESSION['id'], PDO::PARAM_STR);

$stmt->execute();

Is this script dependent on the above $sql?

laserlight

kingdm wrote:
Out of curiosity, can I use this alone in replace for my above query?

No. However, it does remove the need to use mysql_real_escape_string() in the portions of code that you did not show. If you did not use mysql_real_escape_string(), whether directly or indirectly, then this actually fixes an SQL injection vulnerability in your code.

kingdm wrote:
Is this script dependent on the above $sql?

Yes.

I linked to the PDO documentation; you should read it.

kingdm

Thanks a lot for clarifying everything to me silverlight.

Yeah I did used of mysql_real_escape_string(), placed in a function used throughout my project. Now I learned of a new technique to write query in more complex yet two powerful and friendly to read.

So that's all, thanks for keeping me company.