how to get data from database using date between?

Database stored date

item 1: date from :- 10-07-2017 date to :- 20-07-2017
item 2: date from :- 02-07-2017 date to :- 02-08-2017
item 3: date from :- 01-06-2017 date to :- 20-08-2017

input date

start date:- 05-07-2017 end date :- 25-07-2017

here I gave an input date to get data from database. database stored date is the data I want to print. thing is that, which are the items date included in the input date

condition
this are the condition I want to work

start_date < date from && end_date > date_to 
start_date > date from && end_date > date_to 
start_date < date from && end_date < date_to 
start_date > date from && end_date < date_to 

exception case

start_date < end_date < date_to
start_date > end_date > date_to

I want a codeignitor solution . below am giving my model class structure

public function getDate( $start_date = "",$end_date = """)
{
$this->db->select('*');
$this->db->from('user_details');
$this->db->where('');
}


Source: stackoverflow-php

Can I create a new database using PDO without having a default database?

$servername = "ABC";
$username = "DEF";
$password = "GHI";
try {
    $conn = new PDO("mysql:host=$servername;dbname=fruit", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE apple";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Database created successfully<br>";
} catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

Considering the code above, I always have to use an already created database (....mysql:host=$servername;dbname=fruit...) in order to create a new one (i.e. apple). It seems peculiar to me because in order to create a database I need a database. Doesn’t it sound weird?
Is there any way I can avoid it, if I still want to stick with PDO?


Source: stackoverflow-php

Trying to update table fields in PHP [on hold]

Trying to use PHP to update MySQL database but I keep having this error, is it a simple syntax mistake or have I gone wrong somewhere?

Error: Database query failed.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 ‘WHERE student_id = 4, student_forename = ‘Alex’, student_surname = ‘Lee’, studen’ at line 1.

     <?php

 $student_id = 4;
 $student_forename = 'Alex';
 $student_surname = 'Lee';
 $student_year = 12;

 $query = "UPDATE students SET ";
 $query .= "WHERE student_id = {$student_id}, ";
 $query .= "student_forename = '{$student_forename}', ";
 $query .= "student_surname = '{$student_surname}', ";
 $query .= "student_year = {$student_year} ";

$result = mysqli_query($connection, $query);
   //Test if there was a query error
if ($result) {
     echo "Success!";
   } else {
     die("Database query failed." . mysqli_error($connection));
   }

 ?>


Source: stackoverflow-php

Select SUM and COUNT on same row on PHP and MYSQL

I have two tables, clients and transactions, I´m trying to show on the same row the SUM and COUNT of all the transactions from the same client, and type of transactions, but I don’t know how to group it so it only shows the totals of one client and on the next row the total for another client withouth repeting it. Thank you for your time.

table clients

id_client – client_name

table transacctions

id_transaction – client_id – date – type – amount

   $query = " SELECT * FROM  transacctions, clients
    WHERE (transacctions.date BETWEEN '$date1' AND '$date2') 
    AND transactions.client_id = clients.id_client 
    AND transactions.type = '$type' ; "


     <th>Client Name</th>
     <th>Transaction Type</th>
     <th>Number of Transactions</th>
     <th>total amount</th>


       $output.='<td>'.$row['client_name'].'</td>';
       $output.='<td>'.$row['type'].'</td>';
       $output.='<td>'.$row['SUM(amount)'].'</td>';
       $output.='<td>'.$row['COUNT(amount)'].'</td>';


Source: stackoverflow-php

providing placeholder values for sql paramters

The Problem:

I am using a framework which at many points provides queries just like this one:

$sql = "select $sArticleTable.* from table_A left join " .
       "$sArticleTable on table_A.objectid=$sArticleTable.id " .
       "where table_A.articlenid = $sArticleId " .
       "and $sArticleTable.id is not null and " . $object->someMoreSQL() .
       "order by table_A.sort";

As you can see, one table name gets inserted via the $sArticleTable variable. Even though I highly detest this way of writing up an MySQL-statement, there is nothing right now I can do about it.

This way of writing up the statement prevents PHPStorm from recogizing the table name correctly, so no autocomplete suggestions are being shown.

How can I give PHPStorm a placeholder value for the variable $sArticleTable so that it will in turn again provide autocomplete suggestions?

I have tried Database -> User Parameters in the settings, but I do not get prompted to enter a placeholder value.

Also know that could simply replace the variable with the correct table name, adjust the SQL and then reenter the variable. But this seems rather unprofessional, given that I think PHPStorm might provide the solution here.


Source: stackoverflow-php

how do i send all item id at once through url success.php?id=

As i add some items in the cart i cant change the status in the database from “ADDED TO CART ” to “CONFIRMED”

<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<?php
require 'includes/common.php';

if (!isset($_SESSION['email']))
{
    header('location: index.php');
}
?>
<html>
    <head>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
    <!--jQuery library--> 
    https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js
    <!--Latest compiled a>
        <meta charset="UTF-8">nd minified JavaScript--> 
    https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js
    <link href="style.css" rel="stylesheet" type="text/css"/>
        <title>Lifestyle Store | Products</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body >
          <?php
        include 'includes/header.php';
        ?>
        <?php
        $user_id=$_SESSION['id'];
        //changed from above user_id to $_getc ['user_id']
        $items_query="SELECT items.id,items.name,items.price from items INNER JOIN user_items INNER JOIN users ON items.id=user_items.item_id AND user_items.user_id=users.id WHERE user_items.user_id= $user_id AND user_items.status='Added To Cart'";
        
        // chaged from select * from user_items inner join items on user_items.item_id=items.id where items.id=$user_id";
        
        $items_query_result= mysqli_query($con, $items_query) or die(mysqli_error($con));
        
        
        
        ?>
        
       
       
        
       <?php  if (mysqli_num_rows($items_query_result)==0){ ?> 
       <?php echo 'Add something in cart';  }  else  { ?>
        
</div> </div> <?php }?> <?php include 'includes/footer.php'; ?> </body> </html>
  1. this is the code for a shopping cart
  2. user add some this in the cart
  3. **then when the user goes to the cart the cart displays all the items with their ids, name and the price **
  4. and finally the total price
  5. **i have defined a while loop to sum up the price and to display the id name etc **
  6. now i want to send the item id to the success.php to change the status in the database from "Added To Cart " to "Confirmed"
Source: stackoverflow-php

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