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

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

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

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

PHP PDO I can’t insert the numbers

I am new with php. I am trying to do a little game.

My purpose is , php will pick 5 number with rand function , and that numbers will add database.

I will show that numbers in index.php
Example :
Today : 1 , 23 , 24 , 31 , 45
Yesterday : 3 , 5, 12 , 31 , 34

but i can’t insert the numbers into the db.

Here is the code and database ;

<?php
include 'config.php';
$ayarsor=$db->prepare("select * from bet where lot_id=?");
$ayarsor->execute(array(0));
$ayarcek=$ayarsor->fetch(PDO::FETCH_ASSOC);



$lot_1 = rand(1,35);
$lot_2 = rand(1,35);
$lot_3 = rand(1,35);
$lot_4 = rand(1,35);
$lot_5 = rand(1,35);

echo $lot_1;
echo $lot_2;
echo $lot_3;
echo $lot_4;
echo $lot_5;


   $ekle = exec("INSERT INTO bet (lot_1,lot_2) VALUES ('$lot_1','$lot_2')");


?> 

This is my database ;
http://prntscr.com/eav1td


Source: stackoverflow-php