Jahuran posted:

I want to make a search query that allows me to say: Give me all the recipeIDs that contain eggs,flour,water, etc. However this would require me to query the same table columns multiple times. I'm not completely sure on how to set up a query like that.

Jahuran posted:

I'm having some trouble with a php mysql query / database layout I want to set up. Perhaps someone can point me in the right direction.

...


I want to make a search query that allows me to say: Give me all the recipeIDs that contain eggs,flour,water, etc. However this would require me to query the same table columns multiple times. I'm not completely sure on how to set up a query like that.

Normally I'd say:

code:

SELECT * FROM `ingredientsRecipes` WHERE `ingredientID`='X' AND `ingredientID`='Y';

haywire posted:

PHP msqli question.

Is there a non hack way to deal with dynamic mysqli prepared statements?

Seriously msqli_stmt::bind_param and the bind_result functions are the worst pieces of poo poo functions I have ever had to deal with, ever.

quote:

Is there a non hack way to deal with dynamic mysqli prepared statements?

KuruMonkey posted:

If Begby was correct, ignore this:

If I read you correctly, you want recipes with eggs AND flour AND water? In which case Begby's answer won't be helping you at all, as IN is a shorthand for OR.

What you want is subqueries:

SELECT recipeID FROM recipesIngredients WHERE ingredientID = 'X' AND recipeID IN (SELECT recipeID FROM recipesIngredients WHERE ingredientID = 'Y') AND recipedID IN (SELECT ...

Which basically lets you chain queries together, but have the SQL engine do the lifting for you still.

(caveat: not tested that syntax, but see http://dev.mysql.com/doc/refman/5.0/en/subqueries.html)

Edit: break those tables!
p.s. this was also really an SQL question, there are likely SQL goons that are better at SQL than us PHP goons.

Golbez posted:

Can you be a little more specific? You mean like having a dynamic number of parameters in a statement?

Here's a function I found somewhere that I've been using, borrowed from http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/

php:

<?
public function bindParams($stmt,$params)
{
    if ($params != null) {
        $types = '';                        //initial sting with types
        foreach($params as $param) {        //for each element, determine type and add
            if(is_int($param)) {
                $types .= 'i';              //integer
            } elseif (is_float($param)) {
                $types .= 'd';              //double
            } elseif (is_string($param)) {
                $types .= 's';              //string
            } else {
                $types .= 'b';              //blob and unknown
            }
        }

        $bind_names[] = $types;             //first param needed is the type string
        // eg:  'issss'

        for ($i=0; $i<count($params);$i++) {//go through incoming params and added em to array
            $bind_name = 'bind' . $i;       //give them an arbitrary name
            $$bind_name = $params[$i];      //add the parameter to the variable variable
            $bind_names[] = &$$bind_name;   //now associate the variable as an element in an array
        }

        //call the function bind_param with dynamic params
        call_user_func_array(array($stmt,'bind_param'),$bind_names);
    }
    return $stmt;                           //return the bound statement 
}
?>

I put this in my class that extends mysqli, so I can use it like this:

php:

<?
$stmt = $mysqli->bindParams($mysqli->prepare($query),$queryParams);
?>

where $queryParams is an array of parameters. The function handles figuring out if it's an integer or string or what not, and binds things accordingly.

McGlockenshire posted:

Can't use PDO instead?

haywire posted:

I read somewhere that PDO was significantly slower than mysqli/mysql. If I'm wrong, please say. I really like PDO but this is going to need to be as fast as possible.

quote:

Also, doesn't PDO have pretty much the same issue?

Mr Viper posted:

Does anyone know of a guide to successfully installing phpMyAdmin?

quote:

McGlockenshire posted:

Unless you're making tens of thousands of queries in a single script run and need it NOW NOW NOW NOW NOW NOW NOW you won't notice any slowdown. I've never noticed any performance difference. Even then, if performance is what you're looking for, why are you working with PHP?

quote:

What issue? You have a query, you have the placeholders, you have a list of stuff to replace the placeholders with. Maybe I don't really understand what problem you're encountering...

haywire posted:

Having a variable number of placeholders. IE for a select where IN( ?, ? ) query, but there could be lots of question marks.

Begby posted:

In that case you have to create the select clause dynamically. If there are 10 items that go in the clause, then you add 10 questions marks to the string.

haywire posted:

Yeah that part isn't hard, it is the binding of those parameters to things. I don't think PDO has this issue, on reflection. mysqli_stmt::bind_param has the most loving retarded syntax ever.

McGlockenshire posted:

Ah, yes, now I understand.

Unfortunately I don't know of anything short of a custom solution that fixes the problem. It was one of the things that drove me up the wall when switching to PHP from Perl. With DBI, you can just pass in an array reference, and it'll automatically understand to use that for one and only one placeholder...

haywire posted:

Yeah, it is just that one, poorly written function. I expect you could hack up the extension quite easily, but I don't want my code to only work on modded install.

Begby posted:

You can extend PDO quite easily without hacking the actual install. You just include your PDO class with your code.

Another option is to look at Zend_DB from Zend Framework. Overall I don't like the framework too much, but one thing its got going is that all the parts are completely independent, so you can just use the Zend_DB part and ignore the rest. Its really quite simple to use.

Also, it includes a mysqli and a pdo adapter. So you can write all your code to use Zend_DB, then switch between mysqli or pdo with a simple config change and not have to modify your code.

Treytor posted:

Is there an easy way to mask the source of a download link? For example, I have an file at http://192.168.0.1/soundfile.mp3, but I want it to appear to be coming from http://www.example.com/soundfile.mp3

Can this be done easily?

Thanks

Treytor posted:

Yes you are correct on your assumptions. I guess I just have no idea how to do what you're saying, but it's promising to hear that it is possible.

haywire posted:

I think that is pretty much the money, but for small queries, is doing all that code going to be slowing me down? I'm trying to write a query that would get a list of about 10 items every second (it generates json). It needs to be really fast.

How would you manage the output? (IE making a function that returned an array like in fetch_array or PDO's fetchAll)

Golbez posted:

The output is managed like any other output, we're talking about a dynamic number of variables, not a dynamic number of selected columns...

haywire posted:

Oh, I think I see, that does make sense. Doesn't change the params issue, thought

quote:

Anyone got suggestions on the fluent interfacing query generation thing? I want to do something like

php:

<?
while( $row = $db->select("hi","bye")->from("sup","hmoo")->where("blah","blah") )
{
 echo $row['hi'];
}
?>

And it be done as prepared statements.

Golbez posted:

I'm pretty sure any params issue you have, I've confronted in the past. What issue, you mean the fact that you have to use that big function just to have a dynamic number of params, or ...? Share the issue, we'll work it out Show the query you want generated and we'll figure how to get there.


I know I've seen that done somewhere but I couldn't tell you where off the top of my head.

Personally, I have no use for it, I hate abstraction and love working with the actual queries, though actually some of these things look very useful, like using an associative array for a mass insert in Zend_DB... but on the other hand, I figure, if you're putting it into the array, you can put it into the query. Same effort, and in my opinion, a tiny bit easier to debug.

It would be nice to be able to use this kind of thing for a dynamic number of parameters though, of course. It's really annoying you can't just repeat $mysqli->bind_param for each parameter. (And, for that matter, using an abstraction layer to repeatedly bind new "WHERE"s, so I wouldn't have to care about if "AND" or "WHERE" was used correctly... anyone know anything like that?

haywire posted:

Dynamic # of parameters is exactly what I meant in my exact post.

Say you are getting a string in the format 1,6,3,5,7 from your GET. This can be any amount long. You want to get this as a prepared statement. You can easily make the query using implode or whatever and substituting the numbers with ?. However, you have to use the loving bindParam syntax which is utterly nasty in every shape and form.

haywire posted:

Oh, I think I see, that does make sense. Doesn't change the params issue, thought


Anyone got suggestions on the fluent interfacing query generation thing? I want to do something like

php:

<?
while( $row = $db->select("hi","bye")->from("sup","hmoo")->where("blah","blah") )
{
 echo $row['hi'];
}
?>

And it be done as prepared statements.

Begby posted:

You can do the fluent interface thing with Zend_DB using mysqli as the driver and it will use prepared statements (Also, Zend_DB doesn't saddle you with the gay rear end mysqli::bind_param bullshit).

The problems you are having really aren't that big of a deal and you can solve them most by using Zend_DB, extending Zend_DB, extending PDO, or rolling your own mysqli wrapper.

Writing a fluent interface on your own isn't too difficult, you have an object return itself, the methods each write to arrays in the object, then the execute() method you take all the crap from the arrays and create a fancy SQL statement. Making sure the AND/WHERES get sorted out is as simple as doing implode(' AND ', $valuepairs) and sticking a WHERE in front. Creating the question marks for IN is as simple as doing implode(',', $arrayOfQuestionMarks)

A fluent interface can be done by returning a self reference

code:

class MySuperPimpDBThing()
{
   private $fields = array();
   private $tables = array();
   private $wherePiecesParts = array();

   function Select($fields)
   {
      // add the fields to the array
      return $this;
   }
   
   function Where($field, $value)
   {
      $wherePiecesParts[] = array($field, $value);
      return $this;
   }

   function WhereIn($field, $arrayOfValues)
   {
     // yada yada
     return this;
   }
   
   function __toString()
   {
      // Get the prepared sql statement with place holders
   }

   function GetValuePairs()
   {
      // return an array of all your values indexed by the place holders
      // or in the proper order matching your sql statment
   }

   function execute()
   {
      // This is where all the magic happens
      // use __toString() and GetValuePairs() to build and execute the 
      // query using your api of choice
   }
}

To keep it simple I would just write a thing that generates your where clause, its probably easier just to write out your own join/select SQL.

You should really check out Zend_DB for ideas at the very least. Give PDO a try too. Yeah, its a bit slower, but I don't think its going to be noticeably slower even under heavy load. We use it on a database with about 30 million records under pretty high load where I work. Personally if it came down to it getting slow, I would much rather buy faster hardware than spend the time and headaches to move to mysqli.

haywire posted:

I see what you mean, I do all of this already in this class:

http://github.com/radiosilence/cheapo/blob/ddbea0c21badb639b6fd83dc5ecc85e26f6a791f/cheapo/query.php

But I'm looking at making it an extension of db class or something to make using it less irritating.

I presume Zend_DB is part of the Zend framework? If so, do I have to use Zend framework in order to use Zend DB?

What do people think of out the Zend framework in general?

Begby posted:

Overall I am really not that happy with a lot of the zend framework stuff. However, one thing they did very right was to make it so there is very little interdependency between the modules. So if you want to use Zend_DB, all you have to do is include that one thing in your project, you don't have to use anything else in their framework. (a lot of other frameworks require you to use all their crap) I think you could go so far as to delete everything except the Zend_DB folder and be good to go. (and by the way I already told you this about 15 posts back)

I use Zend_DB within codeigniter for a lot of stuff with no ill effects.

Zend_DB has a fluent interface. I don't use it though, all I use is an extension of the Zend_DB_Adapter class which I added a few minor odds and ends to.

A Flaming Chicken posted:

PHPLinq can do that - http://www.codeplex.com/PHPLinq

haywire posted:

That looks nice. I want to write my own though, that is why I am a little negative towards sessions of Zend. I'm hand rolling every part of my project (apart from the FirePHP debug class and jQuery) as I learn more that way and know exactly how to fix things.

Also I'm adverse to including masses of files and 3rd party things every run because inclusions really slow things down without caching.

Begby posted:

Its good to learn it. However, I think in the long run you should ditch the whole fluent interface for SQL thing. There is a reason why a lot of people don't use that, its nifty and slick, but inflexible.

In the time it takes you to write out the all the code to create a query in a fluent interface you could have written it in raw SQL. Then there will be queries where you have to go tinker with your class, like if you decide you need to do a subquery or a complicated AND clause requiring a lot of parenthesis.

You are better off trying to encapsulate all your database stuff into a set of classes that act as gateways for domain objects to your database with well written SQL instead of trying to code a sweet swiss army knife of sql.

haywire posted:

Cool model class stuff