Including query, bind parameters and bind results

[epseix1]

Thank you to everyone who has contributed to any of my other questions. It has helped me to have a much greater understanding of classes, functions and prepared statements - and their purposes. But one last question, cos I just can't put all the pieces together myself!

 

For the following example of a prepared statement, can somebody show me how I can store the query, bind parameters and bind results valies in a separate file (include.php), include them and "call" them in example.php

 

include.php

<?php
...
?>

example.php

<?
include 'include.php'
$stmt = $mysqli->prepare("SELECT heading, content FROM miscellaneous WHERE id = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($heading, $content);
$stmt->fetch();
$stmt->close();
?>

I would be SO grateful as I have tried to work this out for myself for 3 or 4 days before consulting this forum - with no success!

[gizmola]

Include/require functions basically act as if you had inserted their contents at the point you include them.

 

With that said, having a separate include file does not imply "storage". PHP has "page" scope, which is to say, that one php script can run at a time. When you include files, you're simply adding to the source of the running script.

 

I'm not clear on what you're getting at with this question, but if it pertains to persistence of data across requests/pages, then you need to utilize dbs, files, caches or sessions for that. Including files are really an organizational mechanism in support of DRY/libraries/configuration.

[epseix1]

I have a 20+ page website and every page utilises database information.

 

It's my understanding that prepared statements are there to allow us to reuse common code. With that said example 2 page will use exact same code except it used "keyword" instead of "id" and may require different results such as "heading" and "edited content", hence different query, parameters and results are required.

 

I am trying to keep all my queries etc. in one place to allow easy-editing further down the line. Does this make sense?

 

I am completely open to any advice regarding file placement etc.

[epseix1]

I just want clean and articulate filing system and coding, but I don't know what is considered best practice for this when using prepared statements.

 

I can include a page of different query functions, and only call the right function on the right page - but I still have to manually edit bind parameters and bind results to fit each query on each page...

[Q695]

I do it on the specific page it's used on (unless its a frame query), and it makes development a lot easier for actual development.

[gizmola]

Yes what you say makes sense, but putting a bunch of queries in one file is not the way to go about this.

 

What you instinctively have been seeking is what the MVC pattern provides... especially the "M" or Model portion of that design pattern.

 

What you want to have is a set of Model classes for handling data. I'd recommend one class per Table to start with.

 

The other thing you are looking for is a database class to consolidate your database connection handling and provide yourself an API that reduces the repetition of the database handling.

 

I'd suggest you start with the database class, and then use it in the models. Then in your seperate functional scripts all the sql queries will be consolidated into the model classes.