epseix1

I would have simply edited my previous topic had it not been locked, however I am asking the same TYPE of question - looking at it from a different angle (hope that's OK). I am trying to generate a "past events" page for my CI website, using a MySQL database table called "events". There are 100+ rows of data, some duplicate, that range across 5 major countries - England, Ireland, Norther Ireland, Scotland and Wales - many different major cities - again, including duplicate entries - and venues. I have struggled for a couple of weeks now with how to query and return the data in the following format: <h1>** grouped country **</h1> <h2> ** grouped city **</h2> <p>** grouped venue **</p>Such as: <h1>England</h1> <h2>London</h2> <p>Tower of London</p> <h2>Manchester</h2> <p>Canal Street</p> <p>Old Trafford</p> <h1>Ireland</h1> <h2>Dublin</h2> <p>St. James' Gate</p> <h1>Scotland</h1> <h2>Glasgow</h2> <p>Buchanan Street</p> <p>Tron Theatre</p>I am DELIBERATELY not including any of my code for model, view and controller, as what I have thus far seems to confuse people as to what I'm trying to achieve when they see it. And I'm open to the fact I may be attacking this from the wrong angle altogether... I just need to result the above! It feels as if this SHOULD be a fairly simple thing to achieve, but I NEED HELP!

I am using CI framework for the first time, and I need some help. I am migrating an existing website over to the CI framework and have followed CI's tutorials step-by-step without much trouble, but there are a few roadblocks I can't seem to get past... My database table has 2 columns - "country" and "city" - and I want to return a result in the view file that groups all rows by country, prints that country as a header, and then prints all corresponding cities as a list beneath the relevant country. Ie. <h1>England</h1> <ul> <li>Liverpool</li> <li>London</li> <li>Sheffield</li> </ul>Whereas before without CI framework, I could just write a query, return a result, redefine a variable and throw in a few if statements to achieve the desired result - the mvc structure of CI prevents me from doing so... I gather the key to this mystery lies in the controller and creating some sort of array to manage the data and use in the view file - can somebody please help me with an example? My existing controller is: <?php class Home extends CI_Controller { public function index() { $this->load->model('Default_model'); $data['query'] = $this->Default_model->getResults(); $this->load->view('page_view', $data); } }Existing view file is: <?php if (isset($query)):?> <?php foreach ($query as $row):?> <h1><?=$row->country?></h1> <ul> <li><?=$row->city?></li> </ul> <?php endforeach;?> <?php endif;?>And model: function getResults() { $query = $this->db->query('SELECT * FROM events GROUP BY country, city ORDER BY country, city'); return $query->result(); }Can anybody help me with an example, just to show me how this type of query is handled within the CI mvc structure? Thanks!

Well yes, but then I get a repeated echo of the country for every row of area... I'm after a result like: England -Essex -Gtr. Manchester -Sussex ... As opposed to... England-Essex England-Gtr. Manchester England-Sussex ... Etc.

So I have a massive events table and I want to display them by first grouping the results by country, followed by area etc. etc. Until now I have used queries within queries using the result of the previous query loop in the where of the second query... (Still with me?) $query1 = "SELECT * FROM calendar GROUP BY country ORDER BY country"; $result1 = mysql_query( $query ) or die( mysql_error() ); while ( $row = mysql_fetch_array( $result ) ) { $country = $row["country"]; echo $country; $query2 = 'SELECT * FROM calendar WHERE country="' . $country . '" GROUP BY area ORDER BY area'; $result2 = mysql_query( $query2 ) or die( mysql_error() ); while ( $row = mysql_fetch_array( $result2 ) ) { echo $row["area"]; } } I'm thinking there must be an easier more efficient way of doing this via nested queries or inner join so that everything is in just one query etc. Can somebody advise?

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...

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.

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!

The following code: $stmt->bind_param('s', $strId);How can I define EVERYTHING within the brackets as a single function as opposed to 2 separate variables? Can't seem to get syntax quite right... ... Is there a better method for storing all prepared statement variables, or all stmt code, in a separate "include" file using nested functions? All advice greatly appreciated?

Thank you! Are there any useful links you recommend regarding explaining how to use classes as opposed to functions as you advise for what I'm trying to do?

I am storing the following MySQL prep statement as a function and calling it before its required, however I've either misunderstood the point of a function or I'm using the wrong syntax somewhere. Can somebody advise? function addressArea(){ $addressAreaStmt = $mysqli->prepare("SELECT addressArea FROM events WHERE strId = ? AND dateTime < CURDATE() AND areaCountry = '$areaCountry' GROUP BY addressArea ORDER BY addressArea"); } addressArea(); ... etc. Now I have considered trying to define the function as purely the statement query, but this also fails... function areaCountry(){ $query1 = 'SELECT areaCountry FROM events WHERE strId = ? AND dateTime < CURDATE() GROUP BY areaCountry ORDER BY areaCountry'; } $stmt = $mysqli->prepare($query1); Any help? Cheers!