I need some help with getting this to work. This is what I have: a function on the page like this: <?php function itemContainer($item) { echo ' <div class="itemContainer"> <div class="left"> <a href="#v" class="plate"> <img src="',$item['src'],'" alt="',$item['alt'],'" /> </a> <!--.left --></div> <div class="middle"> <h1>',$item['title'],'</h1> <p>',$item['text'],'</p> <div>',$item['grades'],'</div> <!-- .middle --></div> <div class="right"> ',$item['price'],' <!-- .right --></div> <!-- .itemContainer --></div>'; } $itemList = array( array( 'src' => 'blahblahblah.jpg', 'alt' => 'some text', 'title' => 'more text', 'text' => 'some more', 'grades' => 'and more', 'price' => '<del>$10.00</del><span>FREE</span>' ); foreach ($itemList as $item) itemContainer($item); ?> ), Code (markup): I got some great help on this site which helped me get this far and it works fine. The info in the array is displayed as it should be on the page and Im happy with that. What I am trying to do is "fetch" the data from sql and place it into the array automatically and display the first 20 records on the page, the next 20 on the second page and so on. Also, do I need separate db's for each category (ex: math, reading, writing) or can they be stored in one db and fetched by their column. Say, only results with "reading" in the category column get displayed? Thanks,
Something like this? if($page == "") {$page = "1";} else {$page = $_GET['page'];} $sql = "SELECT * FROM table"; $query = mysql_query($sql); $num = mysql_num_rows($query); $per_page = "20"; $last_page = ceil($num/$per_page); $first_page = "1"; echo "<a href='?page=".$first_page."'>First page</a> "; if($page == $first_page) { echo "Previous ";} else { if(!isset($page)) { echo "Previous "; } else { $previous = $page-1; echo "<a href='?page=".$previous."'>Previous</a> "; } } if($page == $last_page) { echo "Next "; } else { if(!isset($page)) { $next = $first_page+1; echo "<a href='?page=".$next."'>Next</a> "; } else { $next = $page+1; echo "<a href='?page=".$next."'>Next</a> "; } } echo "<a href='?page=".$last_page."'>Last page</a>"; $start = ($page-1)*$per_page; $limit = "LIMIT $start, $per_page"; $sql = "SELECT src,alt,title,text,grades,price FROM table $limit"; $query = mysql_query($sql); echo "<br /><br />"; while($row = mysql_fetch_array($query) or die(mysql_error())) { extract($row); echo "<div class='itemContainer'> <div class='left'><a href='#v' class='plate'><img src='$src' alt='$alt' /></a><!--.left --></div> <div class='middle'> <h1>$title</h1> <p>$text</p> <div>$grades</div> <!-- .middle --></div> <div class='right'>$price<!-- .right --></div> <!-- .itemContainer --></div>"; } Code (markup): Untested, just a quicky with notepad
I gave this a try but got this error: Parse error: syntax error, unexpected $end in .../call.php on line 82 This is the call.php <html> <head> </head> <body> <?php require_once('dbConnect1.php'); $db = dbConnect(); try { // Connect and create the PDO object $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb); $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8 // Define and perform the SQL SELECT query $sql = "SELECT * FROM `worksheets` WHERE `id` IN(1)"; $result = $conn->query($sql); // If the SQL query is succesfully performed ($result not false) if($page == "") {$page = "1";} else {$page = $_GET['page'];} $sql = "SELECT * FROM worksheets"; $query = mysql_query($sql); $num = mysql_num_rows($query); $per_page = "20"; $last_page = ceil($num/$per_page); $first_page = "1"; echo "<a href='?page=".$first_page."'>First page</a> "; if($page == $first_page) { echo "Previous ";} else { if(!isset($page)) { echo "Previous "; } else { $previous = $page-1; echo "<a href='?page=".$previous."'>Previous</a> "; } } if($page == $last_page) { echo "Next "; } else { if(!isset($page)) { $next = $first_page+1; echo "<a href='?page=".$next."'>Next</a> "; } else { $next = $page+1; echo "<a href='?page=".$next."'>Next</a> "; } } echo "<a href='?page=".$last_page."'>Last page</a>"; $start = ($page-1)*$per_page; $limit = "LIMIT $start, $per_page"; $sql = "SELECT jpeg,imgName,title,itemDesc,grades,price FROM worksheets $limit"; $query = mysql_query($sql); echo "<br /><br />"; while($row = mysql_fetch_array($query) or die(mysql_error())) { extract($row); echo "<div class='itemContainer'> <div class='left'><a href='#v' class='plate'><img src='$jpeg' alt='$imgName' /></a><!--.left --></div> <div class='middle'> <h1>$title</h1> <p>$itemDesc</p> <div>$grades</div> <!-- .middle --></div> <div class='right'> <h3>$price</h3> <img class=button src='$button' alt='buttonName' /> <!-- .right --></div> <!-- .itemContainer --></div>; } ?> </body> </html> Code (markup): Any ideas? Thanks guys.
1) If there's more than one of them what makes them the SINGLE heading under which all other headings on the page are a subsection. 2) Why did you add all those classes and extra DIV to elements that don't need them? Might also help if the classes you used weren't presentational since what's 'right' and 'left' or 'middle' on one layout could be arranged entirely differently on another -- hence the whole "HTML is for saying what things ARE, not what they look like" -- you do it the way you changed it you might as well go back to writing HTML 3.2 3) Ignore Gavo's advice (no offense buddy, but DAMN) -- between the broken paging methodology and using the mysql_ functions which we have ZERO business using anymore (as evidenced by the giant red warning box on every mysql_ function's page on php.net that people continue to ignore), it's not just dated, it's non-functional. I put together a demo for you that does what you are asking, tossing it all in a rar file. http://www.cutcodedown.com/for_others/trenttdogg/sqlToMarkup/sqlToMarkup.rar Matching PHPS files in the same directory so folks can follow along: http://www.cutcodedown.com/for_others/trenttdogg/sqlToMarkup/ The dbsettings.php is nothing new, typical function wrap with define so it can only be called once. I generally use more secure methodology than even this -- but I don't think you're quite ready for "one index to rule them all" just yet. I made a simple populate.php -- reduced security since it's just to plug in 200 random records with values. Also illustrates one of the great advantages to prepared queries -- you can reuse a prepared statement over and over plugging in different values. In the template.php I tacked on my rather robust pagination code. The theme_paginationLine function gives us a nice LI and anchors on everything but the current item. The theme_pagination is the one you would actually call. You pass it the current page number, how many items to show per page, the total number of items (not pages), the base URL that should be on every anchor, and a 'params' value which contains things like the current "category=something" -- if any. items.template.phps has no real changes from the code I showed you last time around, apart from changing to $item['description'] since 'text' and 'desc' and their kine are reserved words, you can't use them for fieldnames in queries. The real magic of course is in the demo.phps. It includes the templates in global scope, meaning the database connection isn't passed to them (just in case)... likewise I wrap all the database handling in a function so our $db handler isn't in global scope. Notice that I unset the settings as soon as we've used them -- no sense leaving them around should a code elevation happen. In an ideal world we'd have some code to say only the 'main' function in a single 'index.php' in the same directory as settings could call the dbSettings() function -- even better we'd extend PDO to accept that array on it's constructor instead of separate parameters -- but that's getting into the advanced stuff. I turn off 'EMULATE_PREPARES' becuase it makes prepared queries unable to process LIMIT properly on some systems. It may be slower in some cases, but the native prepare is allegedly more secure anyways... and given the choice between insecure/fast and secure/slower -- I choose secure! I set a value on how many to show per page, change to your liking; I used 5 to keep it simple and make testing the pagination easier. The paginationParams string is for passing category (if any) as the extraParams value on theme_pagination. To show a good pagination you need to know how many there are total, a simple COUNT(*) query does the trick for that. I do a isset on $_GET['category'] to determine which query and query method to use. If you're not passing parameters it's faster to run '->query' than it is to do a prepare/execute for no reason. ->fetchColumn is a great simple way to pull the count off the result. Getting the items is a bit more complex. First we figure out what page we're on, then figure out how many into the database that page would be ($itemOffset)... I build the data array and then conditionally add the category if it's needed. The prepare uses a inline evaluation to figure out if it should add 'WHERE' or not for category -- inline eval is the simplest approach since for some reason mySQL actually cares about the order of statements, expecting LIMIT after WHERE. (pain in the ass). Call the theme, output a heading to show what it is we're looking at, while/->fetch out the items, then call the pagination passing all those valuse we came up with... footer and done! Hope this helps. (and for once I'm posting TESTED code)
Oops, forgot to say it's expecting this structure: CREATE TABLE items ( category VARCHAR(64), src VARCHAR(255), alt VARCHAR(255), title VARCHAR(255), description TEXT, grades VARCHAR(255), price DECIMAL(16,2) ) Code (markup): .. or something similar. My bad for not including that.
I was using the img class and h3 tag to get the look I am after and for so I could keep the image in place. See "http://housedogg.com/test/b-report.php" The item container is made up of 3 parts (left, middle, right) and each one contains certain elements. I didn't know how else to change the font (hence my h3 tag) and keep the properties of the image button (hence class button). If there is a better way to do it, I'm all ears.
@Deathshadow- you're an awesome genius. I'm playing around with it right now and it's pretty cool. Thanks again.
Using a heading to change the font size is just wrong -- you change it in the CSS. Say it with me, HTML is for saying what things ARE, CSS is for saying what they look like. If I've said it before I'll say it again, if you choose your tags based on their default appearance, you are choosing the wrong tags! change the font-size (and the line-height too, never trust inherited/default line-heights) in the CSS. Keeping the image in place is easy -- it's a float. You've got a div next to it, set the div to overflow:hidden and it won't indent. or pass under the float. You want the first line bigger, you go into: .itemContainer h2 { font:bold 100%/140% arial,helvetica,sans-serif; color:#800; } Code (markup): and make it bigger. you want grade bigger, it's in the only child of .content so .itemContainer .content div { color:#008; } Code (markup): Is where you'd set that larger if desired, or add padding, or whatever. Your HTML should say what things ARE -- and you do NOT have multiple headings there. Headings are the START of subsections and typically describe that subsection -- these are all one section unto themselves so you should only have ONE heading, typically the stuff saying what the rest of it is describing and/or about. An H1 is the heading under which all other content on the page is a SUBSECTION, that's why people usually frown on using H1 more than once on a page. H2's indicate subsections of the H1, H3's are subsections of the H2 immediately preceding it. You don't actually HAVE multiple subsections, so there's no reason to be using them. (just as those H4 up above that make even LESS sense). HTML is for what things are, CSS is for what they look like on a media target -- and I say media target because you may have more than one -- another of the reasons for presentation being separate from content. If you're going to use tags just for their appearance, you might as well go back in time to 1997 and HTML 3.2 Admittedly, and as I've said thousands of times, that's where a LOT of developers still have their heads wedged -- making it hard for new people to learn anything given the sheer volume of web rot and decade out of date practices floating around... Which of course is how HTML 5 was birthed.
I agree. I get a lot of my code from other sites and forums and such and I'm finding out that many of the techniques I had been using were quite outdated. I'm totally new to PHP and only have a little CSS experience, but am learning quickly. Thanks again for all your help. Is is possible to "fetch" records from the db based on the columns? For example, say that under "categories" I have math, english, reading, and I want to display only the math worksheets on a page. Can this be done by selecting from items(table) then math(column) or would I need to make a separate table for each category? Thanks again.
I already implemented that for categories... run demo.php?category=math You'll find the pagination updates to reflect the restricted list too.... so the second page is demo.php?category=math&page=1 (or if using my 'populate' stock, category=easy) That's a 1 because it's on the back end set to start at zero. That should probably also be a & not a & -- my bad. Implementing it for other fields would mean trapping them the same way and modifying the SQL "WHERE" statement as appropriate. Sorry there, I should probably have pointed out it already had that -- maybe put some links on the template to show it in action.
YES that's exactly what I was looking for. So if for example I wanted to get results based on price (say 1.00 and below) I would have to change the where statement in the demo.php file? Which line(s) of code need modified? I will actually have quite a few more fields in my table like sub-category (math>addition; math>subtraction, etc), common core, also a date/time stamp that will eventually be used to display the "records 7 days old or less" in their own div called "what's new" or something. I have approx 4000 worksheets that will go into the db. How do I implement a search box to query the db?
I think I missed something. I added a field to the table called subCategory, copied the php code from demo.php and changed the word "category" to "subCategory" on every instance. And it didn't work. Here's the code: <?php require_once('template.php'); require_once('items.template.php'); function main() { require_once('dbSettings.php'); $dbSettings = dbSettings(); $db = new PDO( $dbSettings['dsn'], $dbSettings['user'], $dbSettings['password'] ); unset($dbSettings); // delete it as soon as we're done with it! $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // make PDO work with LIMIT $showPerPage = 10; $paginationParams = ''; // for the pagination /* get the count */ if (isset($_GET['subCategory'])) { $statement = $db->prepare(' SELECT COUNT(*) FROM items WHERE subCategory = :subCategory '); $statement->execute(array( ':subCategory' => $_GET['subCategory'] )); $paginationParams .= 'subCategory='.htmlspecialchars($_GET['subCategory']); } else $statement = $db->query(' SELECT COUNT(*) FROM items '); $itemTotal = $statement->fetchColumn(); /* get the items */ $itemPage = isset($_GET['page']) ? $_GET['page'] : 0; $itemOffset = $itemPage * $showPerPage; $queryData = array( ':page' => $itemOffset, ':perPage' => $showPerPage ); if (isset($_GET['category'])) { $queryData[':subCategory'] = $_GET['subCategory']; } /* 'WHERE' has to go before 'LIMIT' -- stupid if you ask me */ $statement = $db->prepare(' SELECT * FROM items '.( isset($_GET['subCategory']) ? 'WHERE category = :subCategory' : '' ).' LIMIT :page , :perPage '); $statement->execute($queryData); theme_header('SQL To Item with Pagination Demo'); echo ' <h1>Worksheets for ',( isset($_GET['subCategory']) ? htmlspecialchars($_GET['subCategory']).' subCategory' : 'All Sub-Categories' ),'</h1>'; while ($item = $statement->fetch()) theme_itemContainer($item); theme_pagination( $itemPage, $showPerPage, $itemTotal, 'demo.php', $paginationParams ); theme_footer(); } main(); ?> Code (markup): And after I did that I got this error: Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in .......content2.php on line 56 Also, I put this code in a separate file (content2.php) and called it to the demo.php page with an include_once just to clean up the html on the page. Is that ok?
Is it possible to "drill down" further into the db using multiple columns. For example, I have a table called items set up like this: items ( subject VARCHAR(64), category VARCHAR(64), subCategory VARCHAR(64), type VARCHAR(64), src VARCHAR(255), alt VARCHAR(255), title VARCHAR(255), description TEXT, grades VARCHAR(255), price DECIMAL(16,2) ) Code (markup): I would like the records returned that match the following: math(subject), addition(category), placevalue(type), first(grades). Please let me know if this is possible. Thanks.
Given that structure, the best way to handle that would be (IMHO) making an array that lists the fieldnames, then using that array to populate the 'where' query, the data array and the string passed to the URL in the pagination. <?php require_once('template.php'); require_once('items.template.php'); function main() { $whereFields = array( 'subject','category','subcategory' ); $queryData = array(); $queryWhere = array(); $paginationParams = array(); foreach ($whereFields as $fieldName) { if (isset($_GET[$fieldName])) { $queryWhere[] = $fieldName.' = :'.$fieldName; $queryData[':'.$fieldName] = $_GET[$fieldName]; $paginationParams[]='category='.urlencode($_GET['category']); } } $queryWhere = ( count($queryWhere) == 0 ? '' : 'WHERE '.implode(' AND ',$queryWhere) ); $paginationParams = implode('&',$paginationParams); require_once('dbSettings.php'); $dbSettings = dbSettings(); $db = new PDO( $dbSettings['dsn'], $dbSettings['user'], $dbSettings['password'] ); unset($dbSettings); // delete it as soon as we're done with it! $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // make PDO work with LIMIT $showPerPage = 5; /* get the count */ if (empty($queryWhere)) { $statement = $db->query(' SELECT COUNT(*) FROM items '); } else { $statement = $db->prepare(' SELECT COUNT(*) FROM items '.$queryWhere.' '); $statement->execute($queryData); } $itemTotal = $statement->fetchColumn(); /* get the items */ $itemPage = isset($_GET['page']) ? $_GET['page'] : 0; $itemOffset = $itemPage * $showPerPage; /* add the extra values to our query data */ $queryData[':page'] = $itemOffset; $queryData[':perPage'] = $showPerPage; /* 'WHERE' has to go before 'LIMIT' -- stupid if you ask me */ $statement = $db->prepare(' SELECT * FROM items '.$queryWhere.' LIMIT :page , :perPage '); $statement->execute($queryData); theme_header('SQL To Item with Pagination Demo'); echo ' <h1>Results for following restrictions</h1> <pre>',print_r($queryData),'</pre>'; while ($item = $statement->fetch()) theme_itemContainer($item); theme_pagination( $itemPage, $showPerPage, $itemTotal, 'demo.php', $paginationParams ); theme_footer(); } main(); ?> Code (markup): Or something to that effect -- untested code, so there may be a typo or two, but that's how I'd approach it. Again, same single file to handle all your states... Doing it this way means you could even combine the results. ?category=addition&subCategory=advance You can add the fields that are valid to the $whereFields array right at the start. For the grades thing -- I'd consider storing a 'lowGrade' and 'highGrade' number field (treating K as 0, maybe pre-school as -1) so you could do a numeric check. You'd have to format that in the output, but it would make it easier to list things as '0..3' -- someone's looking for 2nd grade, you want K through 3 included in that, so you'd do "lowGrade <= :grade AND highGrade >= :grade" -- just a thought.
Thanks Deathshadow. I tried it and didn't get an error (so no typo's) I did get the following returned on my page Not sure what that means. My guess is I did something wrong, lol. So would I need to create a new php file for each situation (i.e., reading/bookreports/firstgrade and reading/authorstudy/firstgrade, etc)? I did do that for testing and setting up links/nav for each. Please look at "housedogg.com/TF/index.php, the reading "button" (guy sitting by tree) should be live and get you to the page(s) you sent me. I know the site code is probably not up to snuff, but hey, I'm a newbie. I ran into a problem when the worksheet applied multiple grade levels. It only returns results from the db where with a single value. If there is first, second in grades column, then it does not populate the page. Any idea on that one?
Check that lines 61 and 62 got copied properly, the dipshit code formatter here likely mangled it. The array indexes are mangled which seems to be making the query bomb.
Okay. I got it figured out. I missed one of these SELECT COUNT(*) FROM worksheets Code (markup): . It now works. YAY! Using your example above is possible to go another level such as ?category=addition&subCategory=advance&grades=first. I tried it and it returned the same results.