filtering mysql results which are dynamically loaded

I have 3 pages

  • index.php
  • fetch_pages.php
  • script.js

and a list of artists, the get page fetches the results and the script.js asks for more to dynamically load it into index php, but it loads all of the artists.

loading all the artists dynamically took me a long time and I didn’t fully understand all of the code I’ve copied to achieve this, but now as I don’t fully understand it it’s hard to play around with it, but I believe that I’ll learn along the way

what I want to do is instead of loading all the artists to load the results according to user input (if he clicks photographers load photographers, if he enters a search term to look for results LIKE what he searched for)

What I’ve tried is the $_GET method, but it posts the searchterm in the url of index.php, and when I try to use $_GET in fetch_pages.php it doesn’t see the parameter.

my question is how to $_GET the url parameter from index.php to fetch_pages.php

below is my code

index.php

<?php
// including the config file
include('config.php');
?>
<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Setch</title>
  https://code.jquery.com/jquery-3.2.1.min.js
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/css/bootstrap.min.css" integrity="sha384-rwoIResjU2yc3z8GV/NPeZWAv56rSmLldC3R/AZzGRnGxQQKnKkoFVhFQhNUwEyJ" crossorigin="anonymous">
  https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/js/bootstrap.min.js
  <link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
  <link href="//maxcdn.bootstrapcdn.com/font-awesome/4.1.0/css/font-awesome.min.css" rel="stylesheet">
  <link rel="stylesheet" href="css/style.css" />
</head>
<body>
  

PHP if runs twice and generates duplicate records to the MySQL database

I am writing a little piece of code for the one game where users can bid and the biggest bid wins the round (like the auction). There is a 1-minute timer and when it ends, some actions occur. I will write all the details below.

Timer script:

$auctionTimer = 1;

$target_time = strtotime('+' . $auctionTimer .  'minutes', strtotime($r1['date']));
$current_time = time();
$seconds_left = $target_time - $current_time;
$minutes_left = floor($seconds_left / 60);

if (strlen($minutes_left) != 2) {
  $minutes_left = sprintf("%02d", $minutes_left);
}  

$seconds_left -= $minutes_left * 60;
$seconds_left = floor($seconds_left);

if (strlen($seconds_left) != 2) {
  $seconds_left = sprintf("%02d", $seconds_left);
}

Something is bad with the if ($current_time > $target_time && $ragr1['count'] >= 1) { check:

$stmt1 = $pdo->prepare("SELECT id, user_id, user, bid, date FROM 
    tb_auction_bids ORDER BY id DESC");
$stmt1_while = $pdo->prepare("SELECT id, user_id, user, bid, date FROM 
    tb_auction_bids");
$stmt3 = $pdo->prepare("SELECT id, user_id FROM tb_auction_winners ORDER BY 
    id DESC LIMIT 1");
$stmt11 = $pdo->prepare("SELECT id, user_id, user FROM tb_auction_bids ORDER 
    BY id DESC LIMIT 1");
$stmt12 = $pdo->prepare("TRUNCATE TABLE tb_auction_bids");

$agr1 = $pdo->prepare("SELECT COUNT(id) AS count FROM tb_auction_bids");
$agr2 = $pdo->prepare("SELECT SUM(bid) AS sum FROM tb_auction_bids");


$stmt1->execute();
$stmt1_while->execute();
$stmt3->execute();
$stmt11->execute();

$agr1->execute();
$agr2->execute();

$r1 = $stmt1->fetch(PDO::FETCH_ASSOC);
$r3 = $stmt3->fetch(PDO::FETCH_ASSOC);
$r11 = $stmt11->fetch(PDO::FETCH_ASSOC);

$ragr1 = $agr1->fetch(PDO::FETCH_ASSOC);
$ragr2 = $agr2->fetch(PDO::FETCH_ASSOC);

if ($current_time > $target_time && $ragr1['count'] >= 1) {
  $stmt9->execute(array(
    ':user_id' => $r11['user_id'],
    ':user' => $r11['user'],
    ':win' => $ragr2['sum']
  ));
  $stmt6->execute(array(
    ':win' => $ragr2['sum'],
    ':user_id' => $r1['user_id']
  ));
  while ($r1 = $stmt1_while->fetch(PDO::FETCH_ASSOC)) {
    $stmt7->execute(array(
        ':user_id' => $r1['user_id'],
        ':aukciono_id' => $r3['id'],
        ':user' => $r1['user'],
        ':date' => $r1['date'],
        ':bid' => $r1['bid']
    ));
  } to
  $stmt12->execute();
}

I guess I should not post PDO queries because the problem is not about SQL, it is about PHP. If you are not in this page (…/auction, e.g.), when the 1-minute timer ends, it does nothing at all. You have to go again to the …/auction, e.g., to get a reward. Should I use JavaScript to check if the timer ended and then execute the file?

This is how it looks like (video):
https://www.youtube.com/watch?v=_CZcnd3RTNw

Also, I get

“SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘post’
cannot be null” error and I looked into this thread:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘post’ cannot be null

but it did not help. The $r1['aukciono_id'] is defined also as another variables but only the $r1['aukciono_id'] gives an error.

if ($current_time > $target_time && $ragr1['count'] >= 1) { should run only once but it runs twice and generates duplicate MySQL records. Am I doing something bad with PDO or PHP? Maybe it is because of that PDO error? Help me, please.


Source: stackoverflow-php

Reoccurring events on a calendar​ with details

Looking for a solution to have a recurring​ event calendar with details for each event (text based details).

The events just need to be set for the day, specific times do not matter although being able to optimize the events into a route is part of my goal.

Trying to figure out how to do this with PHP/MySQL but very open to solutions in other languages.

Ultimately I want a index that simply shows the current day, with a option to see yesterday and tomorrow and a custom field to look at specific dates.

Events will be weekly, bi weekly, and monthly. Some events are seasonal (happen in winter only), we will simply have a flag for those events so that they do not populate on the index page when it is between may 1- October 1.


Source: stackoverflow-php

Create Trigger in mysql with call to stored procedure and auto increment of a table

i have a DB with multiple Properties which each has its own DB ID. I have a centralized DB to collect all the data coming from those DB’s.

In order to differentiate the data and not running into duplicate use of an auto increment value, i want to run a trigger before each insert.

This trigger should call a stored procedure to get the respective DB ID and concat this with the auto increment value from the respective table.

I know from Oracle you can concat a stored procedure and sequence. Looking for something like this in mysql.

Below is my trigger which throws an error:

CREATE TRIGGER order_item_sequence 
BEFORE INSERT ON order_item
FOR EACH ROW
BEGIN
  IF (NEW.order_item_seq_id IS NULL) THEN
    SET NEW.order_item_seq_id = CONCAT(call my_dbid, NEW.table_id);
  END IF;
END

New.table_id is my auto increment column in table order_item and call my_dbid is my stored procedure.

I tested the stored procedure, it is fine. The error is not giving me a real fact, just saying syntax is not correct. Maybe I am missing something?


Source: stackoverflow-php

Query to calculate beginning stock, stock in, stock out, stock balance in MYSQL by controling inflation(increase, rise, rising) of unity price

Good morning,

I have a mysql database which contains three tables:

  1. Product Table

enter image description here

  1. stockin Table

enter image description here

  1. stockout Table

enter image description here

And I have the below query which works well, and it helps me to generate the legder report on picture 4 below:

SELECT 
  Product.Product_Code,
  Product.Product_Color,
  Product.Product_Model,
  IFNull(QtyBeginningStockIn.Qty_In, 0)-IFNull(QtyBeginningStockOut.Qty_Out, 0) QtyBeginningStock,
  IFNull(UntyBeginningStockIn.Uprice_In, 0) UntyBeginningStock,
  IFNull(BeginningStockIn.Amount, 0)-IFNull(BeginningStockOut.Amount, 0) BeginningStock,
IFNull(QtyPeriodStockIn.Qty_In, 0) QtyStockIn,
IFNull(UntyPeriodStockIn.Uprice_In, 0) UntyStockIn,
IFNull(PeriodStockIn.Amount, 0) StockIn,
IFNull(QtyPeriodStockOut.Qty_Out, 0) QtyStockOut,
IFNull(UntyPeriodStockOut.Uprice_Out, 0) UntyStockOut,
IFNull(PeriodStockOut.Amount, 0) StockOut,
IFNull(QtyBeginningStockIn.Qty_In, 0)-IFNull(QtyBeginningStockOut.Qty_Out, 0)+IFNull(QtyPeriodStockIn.Qty_In, 0)-IFNull(QtyPeriodStockOut.Qty_Out, 0) QtyStockBalance,
IFNull(UntyclosingStockIn.Uprice_In, 0) UntyclosingStockIn,
IFNull(BeginningStockIn.Amount, 0)-IFNull(BeginningStockOut.Amount, 0)+IFNull(PeriodStockIn.Amount, 0)-IFNull(PeriodStockOut.Amount, 0) StockBalance
FROM Product
LEFT JOIN
(
  SELECT 
    SUM(Qty_In) Qty_In,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date < '2012/01/02'
  group by Product_Code
) QtyBeginningStockIn ON QtyBeginningStockIn.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Qty_Out) Qty_Out,
    Product_Code
  FROM Stockout
  WHERE StockOut_Date < '2012/01/02'
  group by Product_Code
) QtyBeginningStockOut ON QtyBeginningStockOut.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    Uprice_In,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date < '2012/01/02'
  group by Product_Code
) UntyBeginningStockIn ON UntyBeginningStockIn.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Amount) Amount,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date < '2012/01/02'
  group by Product_Code
) BeginningStockIn ON BeginningStockIn.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Amount) Amount,
    Product_Code
  FROM StockOut
  WHERE StockOut_Date < '2012/01/02'
    group by Product_Code
) BeginningStockOut ON BeginningStockOut.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Qty_In) Qty_In,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date >= '2012/01/02' AND StockIn_Date < '2012/01/31'
    group by Product_Code
) QtyPeriodStockIn ON QtyPeriodStockIn .Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    Uprice_In,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date >= '2012/01/02' AND StockIn_Date < '2012/01/31'
    group by Product_Code
) UntyPeriodStockIn ON UntyPeriodStockIn .Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Amount) Amount,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date >= '2012/01/02' AND StockIn_Date < '2012/01/31'
    group by Product_Code
) PeriodStockIn ON PeriodStockIn .Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    SUM(Qty_Out) Qty_Out,
    Product_Code
  FROM Stockout
  WHERE StockOut_Date >= '2012/01/02' AND StockOut_Date < '2012/01/31'
    group by Product_Code
) QtyPeriodStockOut ON QtyPeriodStockOut .Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
     Uprice_Out,
    Product_Code
  FROM Stockout
  WHERE StockOut_Date >= '2012/01/02' AND StockOut_Date < '2012/01/31'
    group by Product_Code
) UntyPeriodStockOut ON UntyPeriodStockOut .Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
   SUM(Amount) Amount,
    Product_Code
  FROM StockOut
  WHERE StockOut_Date >= '2012/01/02' AND StockOut_Date < '2012/01/31'
    group by Product_Code
) PeriodStockOut ON PeriodStockOut.Product_Code = Product.Product_Code
LEFT JOIN
(
  SELECT 
    Uprice_In,
    Product_Code
  FROM StockIn
  WHERE StockIn_Date < '2012/01/02'
  group by Product_Code
) UntyclosingStockIn ON UntyclosingStockIn.Product_Code = Product.Product_Code
  1. Ledger report

    enter image description here

And my question is, is it possible to generate a Ledger report which can display all transactions by details include transaction date, instead of summarize transaction like in my report? Which query can I use?. Thank you


Source: stackoverflow-php

PHP holds only first data-userid from a row of 3

I face a problem with my PHP file. I have a table with 3 rows inside. I echo it with <a>, and I put "data-userid" in order to keep an information. However, when I click the <a> tags only from the first, I take the information that it held.

The code:

Code

The #row['id'] is the information that I want to hold.

And the spot that I want to take the information:

Spot

When the 3 rows are shown only from the first <a>, I take the id, and the other 2 don’t alert anything.

I would appreciate if someone can help me. Thanks a lot.


Source: stackoverflow-php

how to join 3 tables count and subtract

i need to join 3 tables

table 1 bus - bus_type, seats_max, departure_time, arrival_time, bus_id(fk)
table 2 trips - departure, destination
table 3 passengers - *

my query

    $sql = "SELECT *
        FROM bus
        INNER JOIN trips WHERE trips.id = bus.bus_id
        AND trips.departure = :departure 
        AND trips.destination = :destination 
        ";

how i join third table passengers and count() all records, i need to subtract bus.seats_max with passengers total rows. I hope someone has solutions i failed on this for last hour!


Source: stackoverflow-php

form button deleting data by clicking it

My web page is a forum where users can post text and images, then have them displayed on the web page. There is a like and dislike button that is output with each post. Everything works fine except when I hit the like or dislike button the post deletes itself; however, the likes are saved in my database, so I can tell there’s nothing wrong with my queries, so is there something wrong with my form?

Here it is,

//not full file
            if (isset($_GET['postid'])) 
            {

                if (!connect::query('SELECT post_id FROM dry_likes WHERE post_id=:postid', array(':postid'=>$_GET['postid']))) 
                {
                       connect::query('UPDATE dry_posts SET likes=likes+1 WHERE id=:postid', array(':postid'=>$_GET['postid']));
                       connect::query('INSERT INTO dry_likes VALUES (null, :postid)', array(':postid'=>$_GET['postid']));
                } 



                if (!connect::query('SELECT post_id FROM dry_dislikes WHERE post_id=:postid', array(':postid'=>$_GET['postid']))) 
                {
                       connect::query('UPDATE dry_posts SET dislikes=dislikes+1 WHERE id=:postid', array(':postid'=>$_GET['postid']));
                       connect::query('INSERT INTO dry_dislikes VALUES (null, :postid)', array(':postid'=>$_GET['postid']));
                } 

            }


            $dbposts = connect::query('SELECT * FROM dry_posts ORDER BY id DESC');
            $posts = "";

            foreach($dbposts as $p){
                    if (!connect::query('SELECT post_id FROM dry_likes WHERE post_id=:postid', array(':postid'=>$p['id']))&&!connect::query('SELECT post_id FROM dry_dislikes WHERE post_id=:postid', array(':postid'=>$p['id']))) {
                            $posts .="<img src='".$p['postimg']."'>".htmlspecialchars($p['body'])."
                            <form action='try.php?postid=".$p['id']."' method='post'>
                                    <input type='submit' name='like' value='Like'>
                                    <span>".$p['likes']." likes</span>
                                    <input type='submit' name='dislike' value='Dislike'>
                                    <span>".$p['dislikes']." dislikes</span>
                            </form>
                            <hr /></br />
                            ";

                    } 

            }

Here’s the form

<form action='try.php' class = "forum" method="post" enctype="multipart/form-data">
<textarea name="postbody" rows="4" cols="60" class = "text">

</textarea>
         <br />Upload an image:
        <input type="file" name="postimg">
        <input type="submit" name="post" value="Post">
</form>


Source: stackoverflow-php

Why its not displaying current time zone?

I am trying to add parameter in query which passes indian time zone to mysql database server.But when i am passing this query it shows 0000-00-00 00:00:00 in my table.
I am using hostinger php my admin database server.
Please help me to solve this.

<?php


include 'confi.php';

 date_default_timezone_set('Indian/Maldives');

    $email = isset($_POST['email']) ? mysqli_real_escape_string($conn,$_POST['email']) : "";
    $today = date("d/m/Y h:i:s A");

    $sql ="INSERT INTO `i_order` (`ID`, `email`,`date`) VALUES (NULL, '$email', '$today');"; 

    $qur = mysqli_query($conn,$sql);
    if($qur){
        $json = array("status" => 1, "msg" => "success!");
    }else{
        $json = array("status" => 0, "msg" => "error!");
    }

@mysqli_close($conn);

/* Output header */
    header('Content-type: application/json');
    echo json_encode($json);


?>


Source: stackoverflow-php

MySQL table name with accent. Invalid utf8 character string when updating via PDO

I’m in a situation where I need to update some rows in a table named “matrículas’. The query looks something like this:

UPDATE `matrículas` SET...

When I run this query in my SQL program (HeidiSQL) directly, it executes without problems. When I do it in PHP via a PDO object, I get the following error:

SQLSTATE[HY000]: General error: 1300 Invalid utf8 character string: 'matrxEDculas'

My PDO object is set up like this:

$db= new PDO(
    'mysql:host='.$credentials['host'].';dbname='.$credentials['dbname'].';charset=utf8',
    $credentials['user'],
    $credentials['password'],
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The actual update is done by taking the above query and doing this:

$query = $this->db->prepare($sql);
$query->execute($params);

Both the table and the database were created using the utf8_general_ci collation.

Any ideas what I’m doing wrong? btw, I’m currently testing in Windows in case that has anything to do with it…


Source: stackoverflow-php