This is the first url: http://localhost/SquprimeRevise/administrator/admin/invoice_edit.php?group_id=7&type=new

invoice_edit.php

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Squprime</title>

<!-- CSS -->
<link href="style/css/transdmin.css" rel="stylesheet" type="text/css" media="screen" />
<!--[if IE 6]><link rel="stylesheet" type="text/css" media="screen" href="style/css/ie6.css" /><![endif]-->
<!--[if IE 7]><link rel="stylesheet" type="text/css" media="screen" href="style/css/ie7.css" /><![endif]-->

<!-- JavaScripts-->
<script type="text/javascript" src="style/js/jquery.js"></script>
<script type="text/javascript" src="style/js/jNice.js"></script>
</head>

<body>
    <div id="wrapper">
        <!-- h1 tag stays for the logo, you can use the a tag for linking the index page -->
        <h1><a href="#"><span>Squprime</span></a></h1>
        <img src="images/logo2.png" height="70px" style="margin: -45px 0 0 -180px; position: absolute;"><br><!--<img src="images/admin.png" height="60px" style="margin: -20px 0 0 430px">--><br><br>
        <!-- You can name the links with lowercase, they will be transformed to uppercase by CSS, we prefered to name them with uppercase to have the same effect with disabled stylesheet -->

        <?php include('top_nav.php'); ?>

        <!-- // #end mainNav -->

        <div id="containerHolder">
            <div id="container">
                <div id="sidebar">

                    <?php include("admin_nav.php"); ?>

                    <!-- // .sideNav -->
                </div>    
                <!-- // #sidebar -->

                <div style="margin: -15px 0 0 230px; width: 700px; position: absolute;">
                <!-- h2 stays for breadcrumbs -->
                <h2><a href="#">Dashboard</a> &raquo; <a href="#" class="active">Invoice</a></h2>
                </div>


                <!-- Insert New -->

<div id="menu">   
      <center>  

      </center>
      <p>&nbsp;</p>

  <p>&nbsp;</p><center>
      <p>

<?php

    include('includes/koneksi.php');

    $group_id = isset($_GET['group_id']) ? $_GET['group_id'] : '';  
    $date = isset($_POST['date']) ? $_POST['date'] : '';  
    $confirmation = isset($_POST['confirmation']) ? $_POST['confirmation'] : '';     
    $group_date = isset($_POST['group_date']) ? $_POST['group_date'] : ''; 
    $group_name = isset($_POST['group_name']) ? $_POST['group_name'] : ''; 
    $group_package = isset($_POST['group_package']) ? $_POST['group_package'] : '';
    $group_level = isset($_POST['group_level']) ? $_POST['group_level'] : '';
    $group_teacher = isset($_POST['group_teacher']) ? $_POST['group_teacher'] : '';
    $group_payment = isset($_POST['group_payment']) ? $_POST['group_payment'] : ''; 


    //Simpan berita 
    if (isset($_POST['ok'])){       
        echo "test";

        if (empty($_GET['group_id']))
            {
            $sqlstr = "INSERT INTO `invoice`(group_id, group_date, group_package, group_level, group_teacher, group_payment) VALUES('".$group_id."','".$group_date."','".$group_package."','".$group_level."','".$group_teacher."','".$group_payment."')";          
            }
        else
            {           
            $sqlstr = "UPDATE `invoice` SET group_date='".$group_date."', group_id='".$_GET['group_id']."', group_package='".$group_package."', group_level='".$group_level."', group_teacher='".$group_teacher."' WHERE group_id='".$_GET['group_id']."'";         
            }       

        $result = mysql_query($sqlstr) or die(mysql_error());

        //Jika mode edit, maka tidak akan dikirimkan konfirmasi kepada subscriber
        //if (empty($_REQUEST['id']))   kirimEmail($idKategori, $judul, $news);
        $confirmation = ($result) ? "Data has been saved." : "Fail to save data.";  
    }

    echo "test2";
    //Load berita
    if (!empty($_GET['group_id']) && (empty($_GET['type']) or $_GET['type']!='new') ){

        $result = mysql_query("SELECT * FROM `invoice` 
        INNER JOIN `group`
        ON invoice.group_id = `group`.group_id
        WHERE invoice.group_id =".$_GET['group_id']) or die(mysql_error());
        $data = mysql_fetch_array($result);     
        $group_id = $data['group_id'];
        $group_date = $data['group_date'];
        $group_name = $data['group_name'];
        $group_package = $data['group_package'];
        $group_level = $data['group_level'];
        $group_teacher = $data['group_teacher'];
        $group_payment = $data['group_payment'];

    }else {

        $result = mysql_query("SELECT * FROM `group` WHERE group_id =".$_GET['group_id']) or die(mysql_error());
        $data = mysql_fetch_array($result); 

        // set all empty for new info except for group name
        $group_id = "";
        $group_date = "";
        $group_name = $data['group_name'];
        $group_package = "";
        $group_level = "";
        $group_teacher = "";
        $group_payment = "";

    }

    ?>

    <div align="center">
        <div style="width:800px;text-align:left;">
        <?php echo '<br><br>'; ?>
        <?php echo $confirmation;?>      
        <form method="post" action="<?php echo $_SERVER['PHP_SELF']."?group_id=".$group_id; ?>">
            <!-- <input type="hidden" name="id" value="<?php// echo $id; ?>"/>--><br><br><br><br><br><br>
            <table>
                <tr>
                    <td>Group Date</td>   
                    <td><input id="datedate" type="text" size="15px" name="group_date" value="<?php echo $group_date; ?>"></td>
                </tr>
                <tr>
                    <td>Group Name <font color="red"></font></td>               
                    <td><input type="text" size="30px" name="group_name" value="<?php echo $group_name; ?>" disabled></td>
                </tr>
                <tr>
                    <td valign="top">Package</td>         
                    <td><?php
                    $row = mysql_query("SELECT * FROM `package`") or die(mysql_error());
                    echo '<select name="group_package">';
                    while($data = mysql_fetch_array($row)){
                        echo '<option value="'.$data['package'].'"';
                        if($group_package == $data['package']){
                        echo 'selected="selected"';}
                        echo '>'.$data['package'].'</option>';
                        }
                    echo '</select>';    
                    ?></td>
                </tr>
                <tr>
                    <td>Level</td>
                    <td><?php
                    $row2 = mysql_query("SELECT * FROM `level`") or die(mysql_error());
                    echo '<select name="group_level">';
                    while($data2 = mysql_fetch_array($row2)){
                        echo '<option value="'.$data2['level
                        _name'].'"';
                        if($group_level == $data2['level_name']){
                        echo 'selected="selected"';}
                        echo '>'.$data2['level_name'].'</option>';
                        }                           
                    echo '</select>';    
                    ?></td>
                </tr>
                <tr>
                    <td>Teacher</td>
                    <td><input type="text" size="30px" name="group_teacher" value="<?php echo $group_teacher; ?>">
                    </td>
                </tr>
                <tr>
                    <td>Payment</td>
                    <td><?php
                    $row2 = mysql_query("SELECT * FROM `group`") or die(mysql_error());
                    echo '<select name="group_payment">';
                    echo '<option value="PAID"';
                    if($group_payment == "PAID"){
                    echo 'selected="selected"';}
                    echo '>PAID</option>';
                    echo '<option value="UNPAID"';                   
                    if($group_payment == "UNPAID"){
                    echo 'selected="selected"';}
                    echo '>UNPAID</option>';                 
                    echo '</select>';    
                    ?></td>
                </tr>

                <tr>             
                    <td></td>
                    <td><br><input type="submit" name="ok" value="Save" class="abutton"/></td>
                </tr>
            </table>
        </form>
        </div>
    </div>
</div> 

<script src="jquery-2.1.1.min.js" type="text/javascript" charset="utf-8"></script>
<script src="jquery.maskedinput.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function() {
  $.mask.definitions['~'] = "[+-]";
    $("#datedate").mask("9999-99-99",{completed:function(){alert("completed!");}});
    $("input").blur(function() {
    $("#info").html("Unmasked value: " + $(this).mask());
    }).dblclick(function() {
    $(this).unmask();
  });
});
</script>

                <!-- End Insert -->  

                <div class="clear"></div>
            </div>
            <!-- // #container -->
        </div>   
        <!-- // #containerHolder -->

        <p id="footer">Feel free to use and customize it. <a href="http://www.perspectived.com">Credit is appreciated.</a></p>
    </div>
    <!-- // #wrapper -->
</body>
</html>

After someone press add invoice to add new invoice, this message appears:

testtest2You 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 1

I wonder why the error appears and which SQL syntax might causes the error.

Start by using custom error messages for your SQLs..

or die("statment 1");
or die("statment 2");

etc. This way you can single out which sql statment is failing.

commented: Agreed ... +0
  1. You are using double quoted strings so you can simplify things. Instead of:

    $sqlstr = "UPDATE invoice SET group_date='".$group_date."', group_id='".$_GET['group_id']."', group_package='".$group_package."', group_level='".$group_level."', group_teacher='".$group_teacher."' WHERE group_id='".$_GET['group_id']."'";

you can write it:

$sqlstr = "UPDATE `invoice` SET group_date='$group_date', group_id='{$_GET['group_id']}', group_package='$group_package', group_level='$group_level', group_teacher='$group_teacher' WHERE group_id='{$_GET['group_id']}'";

The query is far more transparent now and errors are easier to spot.

  1. Using unsanitized user suplied values in your queries is a bad security practice. Validate/sanitize $_GET['group_id'] variable before using it.

  2. Checking $_GET['group_id'] with if(empty($_GET['group_id'])) might not be enough. Maybe you should add an isset() check like:

    if(!isset($_GET['group_id']) || empty($_GET['group_id']))

but I am more or less guessing here since it depends on other code.

Also you can write some temporary debug code to print your query on the screen and then copy and paste it into phpmyadmin (or other DB tool) to check it against your data. Put something like this on line 82:

die('<pre>' . print_r($sqlstr, 1) . '</pre>');

ok, I did what you suggest and I still have the same error.

What is the output if you put this debug code on line 82:

die('<pre>' . print_r($sqlstr, 1) . '</pre>');

invoice_edit.php

Here is part of my code:

//Simpan berita 
    if (isset($_POST['ok'])){       
        echo "test";

        if (empty($_GET['group_id']))
            {
            $sqlstr = "INSERT INTO `invoice`(group_id, group_date, group_package, group_level, group_teacher, group_payment) VALUES('".$group_id."','".$group_date."','".$group_package."','".$group_level."','".$group_teacher."','".$group_payment."')";          
            }
        else
            {           
            $sqlstr = "UPDATE `invoice` SET group_date='$group_date', group_id='{$_GET['group_id']}', group_package='$group_package', group_level='$group_level', group_teacher='$group_teacher' WHERE group_id='{$_GET['group_id']}'";     

            die('<pre>' . print_r($sqlstr, 1) . '</pre>');
            }       

        $result = mysql_query($sqlstr) or die(mysql_error());

        //Jika mode edit, maka tidak akan dikirimkan konfirmasi kepada subscriber
        //if (empty($_REQUEST['id']))   kirimEmail($idKategori, $judul, $news);
        $confirmation = ($result) ? "Data has been saved." : "Fail to save data.";  
    }

    echo "test2";

All the result that I get:

testtest2You 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 1

Put the debug code after the else block:

if (empty($_GET['group_id']))
{
$sqlstr = "INSERT INTO `invoice`(group_id, group_date, group_package, group_level, group_teacher, group_payment) VALUES('".$group_id."','".$group_date."','".$group_package."','".$group_level."','".$group_teacher."','".$group_payment."')";
}
else
{
$sqlstr = "UPDATE `invoice` SET group_date='$group_date', group_id='{$_GET['group_id']}', group_package='$group_package', group_level='$group_level', group_teacher='$group_teacher' WHERE group_id='{$_GET['group_id']}'";
} 

die('<pre>' . print_r($sqlstr, 1) . '</pre>');

I try another one:

Result:

UPDATE invoice SET group_date='2014-10-10', group_id='7', group_package='32 lessons', group_level='', group_teacher='Newton Bale' WHERE group_id='7'

The code is still like my first post (on top - in this discussion).

I wonder why mysql table is not being updated yet.

and no conformation like it should be :

"Data has been saved."

This part of my code I requoted:

invoice_edit.php

//Simpan berita 
    if (isset($_POST['ok'])){       

        if (empty($_GET['group_id']))
            {
            $sqlstr = "INSERT INTO `invoice`(group_id, group_date, group_package, group_level, group_teacher, group_payment) VALUES('".$group_id."','".$group_date."','".$group_package."','".$group_level."','".$group_teacher."','".$group_payment."')";          
            }
        else
            {           
            $sqlstr = "UPDATE `invoice` SET group_date='$group_date', group_id='{$_GET['group_id']}', group_package='$group_package', group_level='$group_level', group_teacher='$group_teacher' WHERE group_id='{$_GET['group_id']}'";     

            }       

        die('<pre>' . print_r($sqlstr, 1) . '</pre>');

        $result = mysql_query($sqlstr) or die(mysql_error());

        //Jika mode edit, maka tidak akan dikirimkan konfirmasi kepada subscriber
        //if (empty($_REQUEST['id']))   kirimEmail($idKategori, $judul, $news);
        $confirmation = ($result) ? "Data has been saved." : "Fail to save data.";  
    }

okay, I figure that part out. Now, I wonder why I cannot record group_level - it remains empty in the table.

Also I wonder why after pressing save button there is no type=new (not even "type") in the url.

invoice_edit.php

<div align="center">
        <div style="width:800px;text-align:left;">
        <?php echo '<br><br>'; ?>
        <?php echo $confirmation;?>      
        <form method="post" action="<?php echo $_SERVER['PHP_SELF'].'?group_id='.$group_id.'&type='.$type; ?>">
            <!-- <input type="hidden" name="id" value="<?php// echo $id; ?>"/>--><br><br><br><br><br><br>
            <table>
                <tr>
                    <td>Group Date</td>   
                    <td><input id="datedate" type="text" size="15px" name="group_date" value="<?php echo $group_date; ?>"></td>
                </tr>
                <tr>
                    <td>Group Name <font color="red"></font></td>               
                    <td><input type="text" size="30px" name="group_name" value="<?php echo $group_name; ?>" disabled></td>
                </tr>
                <tr>
                    <td valign="top">Package</td>         
                    <td><?php
                    $row = mysql_query("SELECT * FROM `package`") or die(mysql_error());
                    echo '<select name="group_package">';
                    while($data = mysql_fetch_array($row)){
                        echo '<option value="'.$data['package'].'"';
                        if($group_package == $data['package']){
                        echo 'selected="selected"';}
                        echo '>'.$data['package'].'</option>';
                        }
                    echo '</select>';    
                    ?></td>
                </tr>
                <tr>
                    <td>Level</td>
                    <td><?php
                    $row2 = mysql_query("SELECT * FROM `level`") or die(mysql_error());
                    echo '<select name="group_level">';
                    while($data2 = mysql_fetch_array($row2)){
                        echo '<option value="'.$data2['level
                        _name'].'"';
                        if($group_level == $data2['level_name']){
                        echo 'selected="selected"';}
                        echo '>'.$data2['level_name'].'</option>';
                        }                           
                    echo '</select>';    
                    ?></td>
                </tr>
                <tr>
                    <td>Teacher</td>
                    <td><input type="text" size="30px" name="group_teacher" value="<?php echo $group_teacher; ?>">
                    </td>
                </tr>
                <tr>
                    <td>Payment</td>
                    <td><?php
                    $row2 = mysql_query("SELECT * FROM `group`") or die(mysql_error());
                    echo '<select name="group_payment">';
                    echo '<option value="PAID"';
                    if($group_payment == "PAID"){
                    echo 'selected="selected"';}
                    echo '>PAID</option>';
                    echo '<option value="UNPAID"';                   
                    if($group_payment == "UNPAID"){
                    echo 'selected="selected"';}
                    echo '>UNPAID</option>';                 
                    echo '</select>';    
                    ?></td>
                </tr>

                <tr>             
                    <td></td>
                    <td><br><input type="submit" name="ok" value="Save" class="abutton"/></td>
                </tr>
            </table>
        </form>
        </div>
    </div>
</div> 

okay, I figure everything out, except for group_level (I still unable to record group_level).

Have you checked that the select element (<select name="group_level">...) has any values? Change default value to something visible (like X) so you can check if it gets saved.

$group_level = isset($_POST['group_level']) ? $_POST['group_level'] : 'X';

The thing is the mysqlstr remains empty:

UPDATE invoice SET group_date='2014-11-10', group_id='7', group_package='32 lessons', group_level='', group_teacher='John Rayne' WHERE group_id='7'

I wonder why?

As I said your select element might have no values or no value was selected. You can test for the former by setting a default value as per my post above (change the code on line 65 of your first post).

ok, I did change the code to what you suggest.

No 'X' is printed and it seems that $group_level has no value.

And did you select any level in the dropdown when you were testing?

You can also put this code on line 58:

echo '<pre>', print_r($_POST, 1), '</pre>';

so the content of the post is displayed. Select values and post the displayed debug info here.

groupinfo_edit.php

<tr>
                    <td>Level</td>
                    <td>
                    <?php //$group_level = 'intermediate 1'; ?>
                    <?php
                    $row2 = mysql_query("SELECT * FROM `level`") or die(mysql_error());
                    echo '<select name="group_level">';
                    while($data2 = mysql_fetch_array($row2)){
                        echo '<option value="'.$data2['level
                        _name'].'"';
                        if($group_level == $data2['level_name']){
                        echo 'selected="selected"';}
                        echo '>'.$data2['level_name'].'</option>';
                        }                       
                    echo '</select>';    
                    ?>


</td>

Another similar error:

Here is the post result:

Array
(
    [group_name] => PT Infracom
    [group_info] => Infracom Group Info
    [group_package] => 32 lessons
    [remaining] => 10
    [group_level] => 
    [group_teacher] => John Rayne
    [group_payment] => PAID
    [ok] => Save
)

I still wonder why only group_level remains empty.

As you can see there is no value for group_level.

  1. Have you selected any value?
  2. Is there a field level_name in the level table?

What is the output of the following queries:

SELECT * FROM `level`;

and

SHOW COLUMNS FROM `level`;

(copy each of the above two queries into phpmyadmin and post the output).

Yes, I did select a value.

level_id level_name package_id
1 advance1 1
2 elementry 1
3 elementry 2
4 intermediate 1
8 intermediate 2

Field Type Null Key Default Extra
level_id int(2) NO PRI NULL auto_increment
level_name varchar(20) NO NULL
package_id varchar(20) NO NULL

The table structure and data looks OK. It is time to have a look a the generated HTML code. Can you post the HTML code for the group_level select element (right click -> View page source -> find the code for the select element in question or use Element inspector).

Mind you, how come you do not use level_id in the option values instead of level_name? This would be more appropriate.

Because I stored the value to another table: group

group_level which is = level_name

HTML output:

    <tr>
    <td>Level</td>
    <td>
    <select name="level_name">
    Notice: Undefined index: level _name in C:\xampp\htdocs\SquprimeRevise\administrator\admin\invoice_edit.php on line 171
    <option value="">advance1</option>
    Notice: Undefined index: level _name in C:\xampp\htdocs\SquprimeRevise\administrator\admin\invoice_edit.php on line 171
    <option value="">elementry 1</option>
    Notice: Undefined index: level _name in C:\xampp\htdocs\SquprimeRevise\administrator\admin\invoice_edit.php on line 171
    <option value="">elementry 2</option>
    Notice: Undefined index: level _name in C:\xampp\htdocs\SquprimeRevise\administrator\admin\invoice_edit.php on line 171
    <option value="">intermediate 1</option>
    Notice: Undefined index: level _name in C:\xampp\htdocs\SquprimeRevise\administrator\admin\invoice_edit.php on line 171
    <option value="">intermediate 2</option>
    </select>
    </td>

</tr>

___________________________

    line 171:  echo '<option value="'.$data2['level
                            _name'].'"';

The output looks just fine when you see the user interface, I wonder why the error appears in the HTML code?

You have a space in your index name:

echo '<option value="'.$data2['level _name']...

The space is caused by a line break. Remove the linebreak and everything should be fine.

The error was not obvious until we looked at the HTML code :-)

Have you managed to correct the above error?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.