The lightweight PHP database framework to accelerate development

How to use join to select conditional data from one table.

For example, look at this query:

select question.id, question.text, vote.vote as myvote from question left join vote on vote.questionId=question.id and vote.userId=2 where question.id=6;

How to achieve similar results with Medoo?

Dear all, I have downloaded the new version but I see this error: Fatal error: Call to a member function quote() on a non-object in C:\sutrsy\conmed.php on line 156 I don't have changed anything on my site and reloaded the old version the script run without problem Some help?

hi~

// Make MySQL using standard quoted identifier
$commands[] = 'SET SQL_MODE=ANSI_QUOTES';

No effect, in version 1.0

so , Generated SQL statement:

SELECT * FROM "yc_category" LIMIT 1

error in mysql version 5.6.13

How to solve?

Thank you!

I just started to use medoo to make my database learning live easier. My table has 5 fields:Primary ID,varchar Unique userid,varchar date,int status,varchar mark. I am able to do 'SELECT', so there is problem with the connection, but when I do insert:

$db = new medoo([...]);

  function insertRecord($database,$userid,$date,$status,$mark=NULL){
    return $database->insert('atdc',[
      "userid" => $userid,
      "date" => $date,
      "status" => $status,
      "mark" => $mark
    ]);
  }

  $r = insertRecord($db,'das','2016-06-12',0,'dad');

  var_dump($r);
  var_dump($db->error());

$r is always 0(if data inserted, it should be the last id), no matter what data I use. But $db->error() returns 00000, which I believe means no error. The data is not inserted, also no error message in PHP error log. I can't figure out what is possibly wrong, please help.

User can do $db->distinct()->select(...) to do a distinct selection (add DISTINCT to the query).

Used code from the post by @darre4nbrown in issue #113.

This simple to use and a crucial feature for interacting with a database.

This change makes it possible to add additional conditions to a join as discussed in #167;

If the addition is a value with the joined table use the modifier [$]:

'right_table_column[$]' => value

If it is a table relation use it as any other join condition

'left_table_column' => 'right_table_column'

As i wrote in title, for Mysql all my requests are KO.

for BAD example generated :

SELECT * FROM "table"

for GOOD example :

SELECT * FROM `table`

if type is mysql, object delimiter (database, table and column) is `

For example, I would like to run a query like this.

SELECT DISTINCT(month), year FROM posts

I haven't found anything in the documentation that addresses this. Perhaps the syntax could be like this:

$medoo->select("posts", array("month", "year"), array("DISTINCT" => "month")); or: $medoo->select("posts", array("DISTINCT month", "year")); or: $medoo->select("posts", array("DISTINCT(month)", "year"));

I don't think this would be too difficult to implement. I would be willing to help out, but I need to study the codebase more carefully first.

Describe the bug Using medoo library to connect to a MySQL database and MSSQL database. It works perfectly with MySQL database but with MSSQL simple select query are failing. This is observed when MSSQL database has collection, schema and table.

In this case, medoo using method tableQuote adds " before and after table name, making select query to fail / return empty results. Details are shared below.

Information

• Version of Medoo: 1.6
• Type of Database (MySQL, MSSQL, SQLite...): MSSQL
• System (Liunx\Windows\Mac): Linux

Detail Code

Name of database is medoo_test

It has a collection named game and that collection has table named users.

So in MSSQL I can execute this query:

USE medoo_test
GO
SELECT TOP 10 user_id FROM [game].[users]
GO

OR

USE medoo_test
GO
SELECT TOP 10 user_id FROM game.users
GO

But SELECT TOP 10 user_id FROM "game.users" will raise error in MSSQL.

In Medoo code, after connecting to database successfully, following line of code is executed:

$users = $db_connection->select('game.users', '*');

But found $users array was empty. So used debug() method to get generated query like:

$query = $db_connection->debug()->select('game.users', '*'); 
echo $query . "\n";

and output show:

SELECT * FROM "game.users"

I found that tableQuote method in Medoo.php file and changed it to

protected function tableQuote($table)
{
        return $this->prefix . $table;
// 	return '"' . $this->prefix . $table . '"';
}

and then executed same code and it produced query without " character surrounding table name, and I also got results as expected.

Expected output I should get results from MSSQL database table users into my php array.

If connection args sended to "new medoo(array(...))" PDO object can be saved in self::$pdo If no args send to "new medoo()" PDO object can be returned from self::$pdo 1 connection - many queries

same in log: can be saved is self::$queries not $this::$queries

Theres really no point to doing it, and the size of the project really isn't a big deal at all. As none of this code gets transferred to the client, theres no bandwidth issue.

Information

• Version of Medoo: 2.1.7
• Version of PHP: 8.2
• Type of Database (MySQL, MSSQL, SQLite...): MySQL
• System (Liunx|Windows|Mac): Linux

Describe the Problem I was running an INSERT query with a string value like '<[email protected]>', and Medoo replaced my < > with quotes leaving it like "xxxxx@xxxx"."com".

After investigating, I found line 664 (function buildRaw), this function is applying a preg_replace to the query: '/(([`']).?)?((FROM|TABLE|INTO|UPDATE|JOIN|TABLE IF EXISTS)\s)?<(([\p{L}_][\p{L}\p{N}@$#-_])(.[\p{L}_][\p{L}\p{N}@$#-_])?)>([^,]*?\2)?/u'

This regex is also modifying string values inside single quotes 'xxxxx', so I asked ChatGPT to fix it 😂 It gave me this:

'/(([`']).?)?((FROM|TABLE|INTO|UPDATE|JOIN|TABLE IF EXISTS)\s)?(?<!')<(([\p{L}][\p{L}\p{N}@$#-])(.[\p{L}][\p{L}\p{N}@$#-])?)>(?!')([^,]*?\2)?/u'

And it works! I haven't tested if it causes other problems, but for now so far everything's working fine and the bug is solved.

Detail Code

Simply run this query:

$db->query("INSERT INTO table ( column ) VALUES ( '<[email protected]>' )");

Expected output It should save exactly what you write, but instead, If you check the database after insertion, you will see: "C5TBEC76-9DC0-4758-9FA1-21DDO6B91D59@mail"."com"

** I know that parameters shouldn't be passed like that, directly in the string, but doesn't change the fact that Medoo shouldn't alter the value in this way, so I assumed it's a bug.

Medoo 2.1.7 | PHP 8.2 | MySQL | Linux

Deprecations

PHP Deprecated:  Using ${var} in strings is deprecated, use {$var} instead in /vendor/catfan/medoo/src/Medoo.php on line 870
PHP Deprecated:  Using ${var} in strings is deprecated, use {$var} instead in/vendor/catfan/medoo/src/Medoo.php on line 1321

Information

• Version of Medoo: 2.1.7
• Version of PHP: 7.3
• Type of Database (MySQL, MSSQL, SQLite...): MySQL
• System (Liunx|Windows|Mac): Windows 10

Describe the Problem Column names other than the first disappear.

Detail Code table test | os | | ---- | | win | | mac | | win | | ios |

$db->get('test', ['pc'=>Medoo::raw("count(<os>='win' OR <os>='mac' OR NULL)")]);

SQL with the second "os" column disappeared is executed

SELECT count("os"='win' OR ='mac' OR NULL) AS `pc` FROM `test` LIMIT 1

desired SQL

SELECT count("os"='win' OR "os"='mac' OR NULL) AS `pc` FROM `test` LIMIT 1

Solution I tried

I thought there was a problem with the "buildRaw" method

the value of the variable in buildRaw method

[$raw->value]
count(<os>='win' OR <os>='mac' OR NULL)

[$matches]
Array
(
    [0] => <os>
    [1] => 
    [2] => 
    [3] => 
    [4] => 
    [5] => os
    [6] => os
)
Array
(
    [0] => 'win' OR <os>='
    [1] => 'win' OR 
    [2] => '
    [3] => 
    [4] => 
    [5] => os
    [6] => os
    [7] => 
    [8] => ='
)

[$query]
count("os"='win' OR ='mac' OR NULL)

"os" is missing here

This issue was resolved by doing the following.

            function ($matches) {
                // add && empty($matches[5])
                if (!empty($matches[2]) && isset($matches[8]) && empty($matches[5])) {
                    return $matches[0];
                }

                if (!empty($matches[4])) {
                    return $matches[1] . $matches[4] . ' ' . $this->tableQuote($matches[5]);
                }

                // add . ($matches[8]??'')
                return $matches[1] . $this->columnQuote($matches[5]) . ($matches[8]??'');
            },

Information

• Version of Medoo:
• Version of PHP:
• Type of Database (MySQL, MSSQL, SQLite...):
• System (Liunx|Windows|Mac):

Describe the Problem A clear and concise description of what the problem is.

Detail Code The detail code you are using causes the problem.

// Write your PHP code here

Most of the features are like insert(), they support array serialization and type auto-detection. Additionally you can use [+], [-], [*] and [/] for mathematical operation.

Expected output A clear and concise description of what output you expected. users table Schema id,username,password,c_date,b_date

I need a SQL like this update users set c_date=b_date; I can't find description on how to do this in the docs.

I tried both doesn't work "c_date" => "b_date", "c_date[=]" => "b_date",

Hello, I just found out that one of the systems where I work uses medoo, searching the documentation I didn't find a method that makes it easy to view variables in the browser.

Not that this is a problem because we can use var_dump() and print_r().

Would it be possible to create an easy native method?

Example: Laravel has a specific short helper function to show variables - ​dd()

similar to debug(), but instead of displaying the SQL, it retrieves the contents of the query.