Barand

January 11, 2020

Have you checked the content of $data[0]?

If it is blank, your query becomes "SELECT * FROM games WHERE id = ", and that would give you that syntax error.

edit:

P.S. You should be using a prepared statement when dealing with POST data

January 11, 2020

One more attempt. Here's a script which puts the hints I gave you into practice. As you can see, it splits the ordered items into separate order depending on the SKU group

SAMPLE:

Code (read, understand what's going on and learn. Refer to php.net manual if you don't know what something does.)

(Not an elseif()..elseif() to be found!)

<?php 
const HOST     = 'localhost';                                                                  #
const USERNAME = '????';                                                                       #
const PASSWORD = '????';                                                                       #
const DATABASE = '????';                                                                       #     These lines would
                                                                                               #
function pdoConnect($dbname=DATABASE)                                                          #     normally be in
{                                                                                              #
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);        #     an included file
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                              #
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                         #
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                                      #
    return $db;                                                                                #
}                                                                                              #

$pdo = pdoConnect();

//
//  CREATE TEST DATA USED BY THIS EXAMPLE                                    
//
$pdo->exec("CREATE TABLE IF NOT EXISTS aveeva (
                id int not null auto_increment primary key,
                order_no int,
                sku int,
                product varchar(50),
                qty int
                )
            ");
$pdo->exec("REPLACE INTO aveeva (id, order_no, sku, product, qty) VALUES
                (1,1020345, 12345, 'Pair of left-footed socks', 2),
                (2,1020345, 35547, 'Square Hula hoop', 1),
                (3,1020345, 12346, 'Pair of right-footed socks', 2),
                (4,1020345, 62347, 'Pair of three-legged tights', 5),
                (5,1020345, 45501, 'String vest', 1),
                (6,1020345, 45501, 'Thermal long johns (red)', 2),
                (7,1020345, 22105, 'Staffordshire pot dog', 2),
                (8,1020345, 38962, '250 Kg dumbell set', 1),
                (9,1020345, 23176, 'Ming vase', 1),
                (10,1020345, 23194, 'Porcelain elephant', 1),
                (11,1020345, 38547, '0.5 metre yoga mat', 1)
            ");


                // DUMMY CLASS TO PROVIDE THE METHODS YOU USED
                class Magee                  
                {
                    private $fromName;
                    private $body;
                    private $subject;
                    private $type;
                    private $sendTo;
                    
                    public function __construct()
                    {
                    }
                    
                    public function setFromName($str)
                    {
                        $this->fromName = $str;
                    }
                    
                    public function setBody($str)
                    {
                        $this->body = $str;
                    }
                    
                    public function setSubject($str)
                    {
                        $this->subject = $str;
                    }
                    
                    public function setType($str)
                    {
                        $this->type = $str;
                    }
                    
                    public function setToEmail($str)
                    {
                        $this->sendTo = $str;
                    }
                    
                    public function send()
                    {
                        //
                        //  Instead of sending emails
                        //  we just build content
                        //  for demonstration output
                        //
                        $out = "<fieldset><legend>TO: {$this->sendTo}</legend>
                                    <label>From</label>{$this->fromName}<br>
                                    <label>Subject</label>{$this->subject}<br>
                                    {$this->body}<br>
                                    ";
                        $out .= "</fieldset>";
                        return $out;
                    }
                } #Magee

// define email addresses for the sku groups
const DEFAULT_ADDY = 'other@gmail.com';
$addys = [ 
          2 => 'abc@gmail.com',
          3 => 'xyz@gmail.com',
          4 => 'qwe@gmail.com'
         ];


$res = $pdo->query("SELECT sku
                         , product
                         , qty
                         , order_no 
                    FROM aveeva
                    ORDER BY order_no, sku     
                   ");
$orders = [];
//
//  Split the items for each order 
//  into groups depending the first
//  digit of their SKU
//
foreach ($res as $r) {
    $ono = array_pop($r);
    $sku1 = substr($r['sku'], 0, 1);
    if (!isset($addys[$sku1])) {
        $sku1 = 0;
    }
    $orders[$ono][$sku1][] = $r;
}

//
//  Build the email bodies from the array data
//
$emailsSent = '';
foreach ($orders as $ono => $odata) {
    
    foreach ($odata as $sku1 => $sdata) {
        $message = "<table border='1'>
                    <caption>Order No: $ono</caption>
                    <tr><th>SKU</th><th>Product</th><th>Quantity</th></tr>\n";
        foreach ($sdata as $item) {
            $message .= "<tr><td>" . join("</td><td>", $item) . "</td></tr>\n";
        }
        $message .= "</table>\n";
        
        $emailsSent .= sendMailbasedOnSku($message, $addys, $sku1);
    }
}


function sendMailbasedOnSku($message, $addys, $sku1)
{
    $emailTemplate = new Magee;
    $emailTemplate->setFromName('GIRI Test mail');
    $emailTemplate->setBody($message);
    $emailTemplate->setSubject("Custom Email from observer");
    $emailTemplate->setType('html');

    // GET THE APPROPRIATE EMAIL ADDRESS
    $sendTo = $addys[$sku1] ?? DEFAULT_ADDY;
    // USE IT
    $emailTemplate->setToEmail($sendTo);
    return $emailTemplate->send();
}
?>
<html>
<head>
<title>Example</title>
<style type='text/css'>
    body     { font-family: clibri, sans-serif; font-size: 11pt;}
    fieldset { width: 70%; margin: 16px auto; padding: 16px;}
    legend   { background-color: black; color: white; padding: 4px;}
    label    { display: inline-block; width: 120px; font-weight: 600;}
    table    { width: 50%; margin: 16px auto; border-collapse: collapse;}
    th       { background-color: black; color: white; padding: 8px;}
    td       { padding: 4px 8px;} 
</style>
</head>
<body>
    <?=$emailsSent?>
</body>
</html>

January 11, 2020

Your problems are mainly because you are trying to write code before you have learnt to read.

In my first post I said you needed 2 things.

The first you have totally disregarded despite my spoonfeeding you the required code.
The second addresses the problem you are now having and has also been ignored.

Good luck and goodbye.

January 10, 2020

53 minutes ago, ginerjm said:

Is there a way to copy the output into a post on the forum?

Did you mean something like this?

/**
* write query results to text table
* 
* @param PDO $pdo
* @param mixed $sql
* @param mixed $params
* @return query results
*/
function pdo2text(PDO $pdo, $sql, $params=[])
{
    $res = $pdo->prepare($sql);
    $res->execute($params);
    $data = $res->fetchAll(PDO::FETCH_ASSOC);
    if (count($data) == 0) {
        return "NO RECORDS FOUND";
    }
    $out = "<pre>\n";
    $heads = array_keys($data[0]);
    $widths = [];
    foreach ($heads as $h) {
        $widths[] = strlen($h);
    }
    foreach ($data as $row) {
        foreach (array_values($row) as $c => $v) {
            $widths[$c] = max($widths[$c], strlen($v));
        }
    }
    $horiz = '+';
    $format = '|';
    foreach ($widths as $w) {
        $horiz .= str_repeat('-', $w+2) . '+';
        $format .= " %-{$w}s |";
    }
    $format .= "\n";   
    $out .= "$horiz\n";
    $out .= vsprintf($format, $heads);
    $out .= "$horiz\n";
    foreach ($data as $row) {
        $out .= vsprintf($format, $row);
    }
    $out .= $horiz;
    
    return $out . '</pre>';
}

echo pdo2text($pdo, "SELECT * FROM ssm.user");

January 10, 2020

Use DateInterval and DatePeriod classes

EG

<?php 

function listDates($data06, $data03)
{
    switch ($data06) {
        case 'annual':
            $interval = 'P1Y';
            $num = 4;
            break;
        case 'half year':
            $interval = 'P6M';
            $num = 9;
    }

    $dates = new DatePeriod(new DateTime($data03), new DateInterval($interval), $num);

    $k = 0;

    foreach ($dates as $d) {
        printf("\n%2d) %s", ++$k, $d->format('Y-m-d') );
    }
}
?>
<html>
<head>
<title>Example</title>
</head>
<body>
    <pre> 
         <?= listDates('annual', '2019-01-01')  ?>
         <br>
         <?= listDates('half year', '2019-01-01') ?>
    </pre> 
</body>
</html>

Results

 1) 2019-01-01
 2) 2020-01-01
 3) 2021-01-01
 4) 2022-01-01
 5) 2023-01-01         

         
 1) 2019-01-01
 2) 2019-07-01
 3) 2020-01-01
 4) 2020-07-01
 5) 2021-01-01
 6) 2021-07-01
 7) 2022-01-01
 8) 2022-07-01
 9) 2023-01-01
10) 2023-07-01

January 10, 2020

I have a similar one which I use to test queries when I can't be bothered to fire up Workbench.

It has some differences to yours

I don't like functions that connect to the db, I prefer to pass the connection
It works for any select query, not hardwired to DESCRIBE (although it will work with that)
Uses prepared queries/parameters

function pdo2html(PDO $pdo, $sql, $params=[])
{
    $res = $pdo->prepare($sql);
    $res->execute($params);
    $data = $res->fetch();
    if ($data) {
        $out = "<table border='1'>\n";
        $heads = array_keys($data);
        $out .= "<tr><th>".join('</th><th>', $heads)."</th></tr>\n";
        do {
            $out .= "<tr><td>".join('</td><td>', $data)."</td></tr>\n";
        } while ($data = $res->fetch());
        $out .= "</table>\n";
    } else {
        $out = "NO RECORDS FOUND";
    }
    return $out;
}

Usage

<?php 
include 'db_inc.php';
$pdo = pdoConnect('humbug');
$role = 2;
?>
<html>
<head>
<style type='text/css'>
    table { border-collapse: collapse; font-family: sans-serif;}
    th    { background-color: #000; color: #FFF; padding: 8px; }
    td    { padding: 4px 8px; }
</style>
</head>
<body>
     <?= pdo2html($pdo, "DESCRIBE ssm_user")?>
     <br>
     <?= pdo2html($pdo, "SELECT * FROM ssm_user WHERE role_id = ?", [$role] )?>
</body>
</html>

Result

image.png.12d01f2b08aea79097cccaab5b4ed4f1.png

January 10, 2020

58 minutes ago, purge08 said:

P cat id is product category meaning the main category. Whereas cat id is the subcategories

That is what I thought. The parent category is a property of the subcategory, not of the product. If you know the subcategory you can derive the parent category. You don't need both.

Similarly, vendor status is a property of the vendor, not the product.

Column names like "product_features" and "product_keywords" implies there may be delimited lists in those columns that should be in separate tables. Ditto the image columns - separate table with a record for each image.

January 10, 2020

I came across this when googling your error message

image.png.b718c2541d0357f14bae8b11cfecc2e7.png

January 10, 2020

Do I take it that this is from Workbench?

Have you checked host, user, password, port settings?

(You do your development on a remote host!?)

January 10, 2020

Have you not mastered copy and paste yet?

// database connection config
$dbHost = 'localhost';
$dbUser = '';
$dbPass = '';
$dbName = '';

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);                                                      //   <-- ADD IT HERE   !!!
                                                                                                                       
$dbConn = mysqli_connect ($dbHost, $dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysqli_select_db($dbConn,$dbName) or die('Cannot select database. ' . mysqli_error());

function dbQuery($sql)
{
    $result = mysqli_query($dbConn,$sql) or die(mysql_error());
    
    return $result;
}

January 10, 2020

Get a copy of MySQL Workbench. There's a right-click menu option to go straight to it.

January 10, 2020

Easiest way is to add this line to your included connection.php

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

immediately before the line that connects to the database (either with mysqli_connect() or new mysqli() )

January 10, 2020

You still haven't ensured that error is always defined

$error = '';                                       // <----------- YOU MISSED HIS LINE TO DEFINE $error
if($check)
{
    header('location:success_package');
    exit;
}
else
{
    $error = '<div class="alert-warning" style="height:60px;">
    <p style="line-height:40px;">Error in posting</p>
    </div>';

}

January 10, 2020

The wrong colours in the code was caused by apostrophe in the text preceding the code. I have moved the text out and code looks pretty now.

January 10, 2020

In this code

if($check)
{
    header('location:success_package');
}
else
{
    $error = '<div class="alert-warning" style="height:60px;">
    <p style="line-height:40px;">Error in posting</p>
    </div>';

}

$error is set only if $check is false.

Later on (where you get the error) you try to echo it when it hasn't been set.

Also, you need to exit() from the script after that header() call to prevent the rest of the script from executing. Change the code to...

$error = '';
if($check)
{
    header('location:success_package');
    exit;
}
else
{
    $error = '<div class="alert-warning" style="height:60px;">
    <p style="line-height:40px;">Error in posting</p>
    </div>';

}

January 10, 2020

@mac_gyver gave the correct way to do it - http_build_query()

EG

$url = 'http://localhost/board/italian.php';

$data = [  'page'    => 2,
           'country' => 'italian',
           'comment' => 'this is a (simple) comment'
        ];
        
$qstr = http_build_query($data);
$url .= '?' . $qstr;

echo $url;                   //--> http://localhost/board/italian.php?page=2&country=italian&comment=this+is+a+%28simple%29+comment

Note the encoding of the query string

January 10, 2020

Use MySQL from the command line client

image.png.86d9b6a54fa3a432e9becf5065237e5e.png

January 10, 2020

1 hour ago, Adamhumbug said:

would you be able to dump the db that you used to create this please

Certainly can...

CREATE TABLE `ssm_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

INSERT INTO `ssm_user` VALUES 
(1,'Michel','Roux',2),
(2,'Rick','Stein',2),
(3,'Jamie','Oliver',2),
(4,'Delia','Smith',2),
(5,'Gordon','Ramsay',2),
(6,'Nigella','Lawson',2),
(7,'Marco','Pierre-White',2),
(8,'Margaret','Thatcher',1),
(9,'Mark','Zuckerberg',1),
(10,'Bill','Gates',1),
(11,'Boris','Johnson',1),
(12,'Alan','Sugar',1);

January 10, 2020

I suspect that your "products" table is need of normalizing. I have marked with an "X" the bits that give me cause for concern.

CREATE TABLE `products` ( 
  `product_id` int(10) NOT NULL, 
  `vendor_id` text NOT NULL, 
X `p_cat_id` int(10) NOT NULL, 
  `cat_id` int(10) NOT NULL, 
  `manufacturer_id` int(10) NOT NULL, 
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `product_title` text NOT NULL, 
  `product_seo_desc` text NOT NULL, 
  `product_url` text NOT NULL, 
X `product_img1` text NOT NULL, 
X `product_img2` text NOT NULL, 
X `product_img3` text NOT NULL, 
  `product_price` int(10) NOT NULL, 
  `product_psp_price` int(100) NOT NULL, 
  `product_desc` text NOT NULL, 
X `product_features` text NOT NULL, 
  `product_video` text NOT NULL, 
X `product_keywords` text NOT NULL, 
  `product_label` text NOT NULL, 
  `product_type` text NOT NULL, 
  `product_weight` decimal(10,1) NOT NULL, 
  `product_views` int(10) NOT NULL, 
X `product_vendor_status` text NOT NULL, 
  `status` varchar(255) NOT NULL 
)

January 10, 2020

Here's my solution using jQuery/ajax. You can add multiple new rows to each section. Each time a row is added those staff already allocated are disabled in the new row's menu.

<?php
const HOST     = 'localhost';                                                                  #
const USERNAME = '????';                                                                       #
const PASSWORD = '????';                                                                       #
const DATABASE = '????';                                                                       #     These lines would
                                                                                               #
function pdoConnect($dbname=DATABASE)                                                          #     normally be in
{                                                                                              #
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);        #     an included file
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                              #
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                         #
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                                      #
    $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);                                     #
    return $db;                                                                                #
}                                                                                              #

$db = pdoConnect('humbug');

//
// HANDLE AJAX REQUESTS
//
if (isset($_GET['ajax'])) {
    
     exit(staffRow($db, $_GET['dt'], $_GET['role'], $_GET['used']));
}

//
//  HANDLE POST DATA
//
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    
    // This where you handle the schedule updates.
    // for now we just output the posted data to check all is OK
    echo 'Posted data<pre>';
    printf("%-15s%10s%10s%10s%10s\n\n", 'Date', 'Role', 'ID', 'Start', 'End');
    foreach ($_POST['form'] as $date => $ddata) {
        foreach ($ddata as $role => $rdata) {
            foreach ($rdata['staff'] as $k => $id) {
                $start = $rdata['starttime'][$k];
                $end = $rdata['endtime'][$k];
                printf("%-15s%10s%10s%10s%10s\n", $date, $role, $id, $start, $end);
            }
        }
    }
    echo '</pre>';
}

//
//  HANDLE GET DATA
//
$wkstart = $_GET['wkstart'] ?? date('Y-m-d');

$heads = ['Name', 'Start Time', 'End Time'];
$sections = [ 1 => 'Management', 
              2 => 'Chefs'
            ];
$daterange = new DatePeriod(new DateTime($wkstart), new DateInterval('P1D'), 6);

$tdata = '';
foreach ($daterange as $date) {
    $dthead = $date->format('l - jS F Y');
    $dt = $date->format('Y-m-d');
    $tdata .= "<tr><th colspan='3' class='w3-black w3-padding'>$dthead</th></tr>\n";
    
    foreach ($sections as $role => $rolename) {
        $tdata .= "\n<tbody id='$dt$role'>
                    <tr><th colspan='3' class='rolename'>$rolename
                    <a href='#' class='w3-right addrow' data-dt='$dt' data-role='$role'>Add row</a>
                    </th>
                    </tr><tr>";
        foreach ($heads as $h) {
            $tdata .= "<th>$h</th>";
        }
        $tdata .= "</tr>\n";
        $tdata .= staffRow($db, $dt, $role);
        $tdata .=  "\n</tbody>\n";
    }
}

function staffRow(PDO $db, $dt, $role, $used='')
{
    return "
           <tr><td>" . staffOptions($db, $dt, $role, $used) . "</td>
           <td><input type='time' name='form[$dt][$role][starttime][]' value='' class='w3-input w3-border'></td>
           <td><input type='time' name='form[$dt][$role][endtime][]' value='' class='w3-input w3-border'></td>
           </tr>\n";
}

function staffOptions(PDO $db, $dt, $role, $used='')
{
    $scheduled = explode(',', $used);
    $opts = "<select class='w3-input'  name='form[$dt][$role][staff][]'>
             <option  value=''> -- select staff name --</option>
            ";
    $res = $db->query("SELECT user_id
                            , firstname
                            , lastname
                       FROM ssm_user
                       WHERE role_id = $role     
                      ");
    foreach ($res as $r) {
        $dis = in_array($r['user_id'], $scheduled) ? 'disabled' : '';
        $opts .= "<option $dis value='{$r['user_id']}'>{$r['firstname']} {$r['lastname']}</option>\n";
    }
    return $opts; 
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Example Staff Schedule</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
    $().ready( function() {
        $(".addrow").click( function() {
            var dt = $(this).data('dt')
            var role = $(this).data('role')
            var sectionid = dt+role;
            var sect = $("#"+sectionid)
            var used = []
            $.each($(sect).find("select"), function(k,v) {
                if ($(v).val() != '') {
                    used.push( $(v).val() )
                }
            })
            if (used.length > 0) {
                $.get(
                    "",
                    {"ajax":1, "dt":dt, "role":role, "used": used.join(",")},
                    function (resp) {
                        $(sect).append(resp);
                    },
                    "TEXT"
                )
            }
        })
    })
</script>
<style type="text/css">
    body  { font-family: calibri, sans-serif; }
    table { border-collapse: collapse; font-size: 10pt; width: 90%; margin: 30px auto;}
    th,td { padding: 4px; width: 33%; background-color: #EEE; border: 1px solid #D0D0D0; }
    input[type='time']  { padding-right: 16px; text-align: center; }
    option:disabled { color: #F2D335; }
    .rolename { background-color: #D8D8D8; }
    .addrow   { text-decoration: none; background-color: #54BC54; border: 1px solid #000; padding:2px 8px; }
</style>
</head>
<body>
<header class="w3-container w3-black w3-padding">
    <h1>Example Staff Schedule</h1>
    <form method='GET'>
    <label>Week commencing </label>
    <input type='date' name='wkstart' value='<?=$wkstart?>'>
    <input type='submit' name='btnSub' value='Submit'>
    </form>
</header>
<form method='POST'>
<table border='1'>
    <?=$tdata?>
    <tr><td colspan='3' class="w3-center"><input type='submit' class="w3-button w3-blue" name='btnSub2' value='Update'></td></tr>
</table>
</form>
</body>
</html>

January 10, 2020

$addys = [ 
          2 => 'jeevarathinamit@gmail.com',
          3 => 'aveevacool@gmail.com',
          4 => 'zus710@gmail.com'
         ];

// CHECK SKU FIRST DIGIT
$chk_sku=(int)substr($sku, 0, 1);
// GET THE APPROPRIATE EMAIL ADDRESS
$sendTo = $addys[$chk_sku] ?? 'giriwebsiteonline@gmail.com';
// USE IT
$emailTemplate->setToEmail($sendTo);
return $emailTemplate->send();

January 9, 2020

On 1/6/2020 at 10:01 PM, Barand said:

I suggest you put this line of code (if you haven't already) just before you connect to the db server

January 9, 2020

Then you have several SELECT queries that are failing.

21 minutes ago, purge08 said:

i put that code in my script above insert products part

That is not where I told you to put it.

January 9, 2020

Have you checked your error log to if there are any messages there?

January 9, 2020

You will need JS, definitely, and maybe ajax.

One approach is to make each management and chefs section a separate <tbody>..</tbody> section. That way you have something you can append to.

Sign In

Barand

Posts

Joined

Last visited

Days Won

Content Type

Profiles

Forums

Posts posted by Barand

Search my database (not working)

PHP mail function how to set sender mail id based on if condition?

PHP mail function how to set sender mail id based on if condition?

Show DB Structure in a table

How to create a set of data from string to insert in MySQL

Show DB Structure in a table

Need help on getting products to insert into database

Show DB Structure in a table

Show DB Structure in a table

I'm facing database submission issues

Show DB Structure in a table

I'm facing database submission issues

I'm facing database submission issues

I'm facing database submission issues

I'm facing database submission issues

query string problem is whooping my behind..help please!

Show DB Structure in a table

Create new line of dynamically populated content from button

Need help on getting products to insert into database

Create new line of dynamically populated content from button

PHP mail function how to set sender mail id based on if condition?

Need help on getting products to insert into database

Need help on getting products to insert into database

Need help on getting products to insert into database

Create new line of dynamically populated content from button

Browse

Activity

Important Information