Jump to content

AdRock

Members
  • Posts

    913
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

AdRock's Achievements

Advanced Member

Advanced Member (4/5)

1

Reputation

  1. Have a look at this PDO class. https://www.culttt.com/2012/10/01/roll-your-own-pdo-php-class/ I started with MySQLi and within a few hours switched to PDO. People I work with like MySQLi and they can't see the benefit of PDO. Drives me nuts ?
  2. Just looking for some advice on how to do this. I can probably code it if someone tells me the best way to do this I want to connect to the database and from one field in the database (e.g. colours) I want to get all possible values and count how many times each value occurs. I then want to create an associative array with each vale and how many times that value occured e.g. Red => 10 Yellow => 5 Green => 3 Blue => 7 I know I can create an array of the colours (or whatever i want) and I know I can comibe the arrays but how would I get each value to increment? Would I use a switch statement inside the while loop from query that checks the value and increments the variable assingned to the value each time it occurs while ($row = mysql_fetch_array($result) { switch($row['field']) { case 'red': $red++; break; case 'yellow': $yellow++; break; case 'green': $green++; break; case 'blue': $blue++; break; default: } } If i do it that way how do i get all those variables into an array so i can comibne the 2 arrays? Any help or advice appreciated
  3. I have 2 tables i need to join but one of the tables sql has DATE_FORMAT in it. SELECT f.festivalid, f.eventname, f.url, DATE_FORMAT(f.datefrom, '%Y-%m-%d') as f.datefrom, DATE_FORMAT(f.dateto, '%Y-%m-%d') as f.dateto,lf.location, f.postcode, f.additional, f.lat, f.lon, f.conname, f.address1, f.address2, f.city, f.county, post, f.telephone, f.mobile, f.fax, f.email, f.dances, f.pub, f.disability, f.caravan, f.campsite, f.indoor, f.car, f.train, f.bus, v.total, v.votes FROM festivals as f, votes as v WHERE f.festivalid = v.festivalid I get an error message pointing to the DATE_FORMAT....any ideas how to fix this?
  4. I have been tearing my hair out all night with this problem and I can't see any logical explanation for it. I have a pagination function that works perfectly normal unless I use a mysql full text search on it. I have just tested it commenting out the SQL with the full text search and it works but as soon as i put the full text SQL back in it doesn't work. I have viewed the source and the function is being called but it's not displaying the links. It just creates and empty <ul> Here is the code in question <?php require_once('php/database/MySQL.php'); require_once('php/database/connection.php'); function pagination_one($total_pages,$page){ // Global variable passed between the pages global $webpage; // Maximum number of links per page. If exceeded, google style pagination is generated $max_links = 10; $h=1; if($page>$max_links){ $h=(($h+$page)-$max_links); } if($page>=1){ $max_links = $max_links+($page-1); } if($max_links>$total_pages){ $max_links=$total_pages+1; } echo '<div class="page_numbers"> <ul>'; if($page>"1"){ echo '<li class="current"><a href="'.$webpage.'.php?pagenum=1">First</a></li> <li class="current"><a href="'.$webpage.'.php?pagenum='.($page-1).'">Prev</a></li> '; } if($total_pages!=1){ for ($i=$h;$i<$max_links;$i++){ if($i==$page){ echo '<li><a class="current">'.$i.'</a></li>'; } else{ echo '<li><a href="'.$webpage.'.php?pagenum='.$i.'">'.$i.'</a> </li>'; } } } if(($page >="1")&&($page!=$total_pages)){ echo '<li class="current"><a href="'.$webpage.'.php?pagenum='.($page+1).'">Next</a></li> <li class="current"><a href="'.$webpage.'.php?pagenum='.$total_pages.'">Last</a></li> '; } echo '</ul> </div>'; } //if (!empty($_POST['keywords'])) $_SESSION['keywords'] = $_POST['keywords']; //$keywords = strtolower($_SESSION['keywords']); //$uckeywords = ucwords($_SESSION['keywords']); $keywords = "festival"; $path = "test"; $webpage = basename($path); $db = & new MySQL($host,$dbUser,$dbPass,$dbName); if (isset($keywords)) { /*$count = "SELECT count(*), MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE) as score FROM festivals WHERE MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE) group by festivalid"; $sql = "sELECT eventname, location, postcode, additional, MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE) as score FROM festivals WHERE MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE)";*/ $count = "SELECT count(*) FROM festivals"; $sql = "sELECT eventname, location, postcode, additional FROM festivals"; } // Perform a query getting back a MySQLResult object $res = $db->query($count); $result = $db->query($sql); //get the number of rows in datatbase $getresult = $result->size(); $numrows = $res->fetchrow(); if(isset($_GET['pagenum'])?$page = $_GET['pagenum']:$page = 1); $entries_per_page = 1; $total_pages = ceil($numrows[0]/$entries_per_page); $offset = (($page * $entries_per_page) - $entries_per_page); $sql = "sELECT eventname, location, postcode, additional, MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE) as score FROM festivals WHERE MATCH(eventname, location, postcode, additional) AGAINST('$keywords*' IN BOOLEAN MODE) order by score desc LIMIT $offset,$entries_per_page"; // Perform a query getting back a MySQLResult object $result = $db->query($sql); $err = $result->size(); if($err == 0) { echo ("No matches met your criteria."); } else { while ($row = $result->fetch()) { $eventname = html_entity_decode(ucwords($row['eventname'])); $location = html_entity_decode($row['location']); $additional = html_entity_decode($row['additional']); $placeholders = array($keywords, $uckeywords); $vals = array("<span class='searchbold'>$keywords</span>", "<span class='searchbold'>$uckeywords</span>"); $link = str_replace($placeholders, $vals, $eventname); $loc = str_replace($placeholders, $vals, $location); $add = str_replace($placeholders, $vals, $additional); echo "<p class='searchlink'><a href='festival.php?festival=".strtolower(str_replace(' ','-',$eventname))."'>".$link."</a>"; echo " - ".$loc."</p>"; echo "<p class='searchinfo'>".$add."</p>"; echo "<p class='searchurl'>http://www.folktaskforce.com/festival.php?festival=".strtolower(str_replace(' ','-',$eventname))."</p>"; } //or after the results pagination_one($total_pages,$page); } echo "</div>"; ?>
  5. I had an idea but i don't know if it's possible. Can I check to see if the variable to be selected from the database is empty and if it is don't add it to the query in the where clause otherwise selected from the database. If i try and select a field with no value in the variable it won't return any rows. The only way i can think of is doing it this way i have done but that seems unnecessary as I should be able to use one query and only add to the WHERE clause any fields that are to be searched
  6. I have a search form with a field for keywords (which uses a full text search on 4 columns), a drop down list and another text field where a user can enter a time. At the minute, I can search using keywords, search by the drop down list, or search by the time field or I can leave them all blank and return all the results but i can't do any of them together I want to be able to use all the fields or just some of them to filter the results but i'm having trouble. If a text field is empty or there is no value selected from the drop down when the form is posted, i want it to ignore those fields in the database. How would i combine all the queries so i can do it all at once or is there a more efficent way of doing this maybe by a switch statement if(!empty($address)) { $result = mysql_query("SELECT id,userid,seats_available,start_street,start_postcode,start_lat,start_long, end_street,end_postcode,end_lat,end_long,TIME_FORMAT(depart_time,'%H:%i') as depart, MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address' IN BOOLEAN MODE) as score FROM carshare WHERE MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address*' IN BOOLEAN MODE) ORDER BY score ASC LIMIT $offset,$entries_per_page"); } else if(!empty($seats)) { $result = mysql_query("SELECT id,userid,seats_available,start_street,start_postcode,start_lat,start_long, end_street,end_postcode,end_lat,end_long,TIME_FORMAT(depart_time,'%H:%i') as depart FROM carshare WHERE seats_available='$seats' ORDER BY id ASC LIMIT $offset,$entries_per_page"); } else if(!empty($departure)) { $result = mysql_query("SELECT id,userid,seats_available,start_street,start_postcode,start_lat,start_long, end_street,end_postcode,end_lat,end_long,TIME_FORMAT(depart_time,'%H:%i') as depart FROM carshare WHERE depart_time='$departure' ORDER BY id ASC LIMIT $offset,$entries_per_page"); } else { $result = mysql_query("SELECT id,userid,seats_available,start_street,start_postcode,start_lat,start_long, end_street,end_postcode,end_lat,end_long,TIME_FORMAT(depart_time,'%H:%i') as depart FROM carshare ORDER BY id ASC LIMIT $offset,$entries_per_page"); }
  7. I have a form with a select box <select name="seats" class="form" style="width:75px"> <option value="any" selected>Any</option> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> <option value="5">5</option> </select> and this is the $_POST $seats = $_POST['seats']; when i $_REQUEST on the next page and echo out the result, i always get 1.....why is this?
  8. thanks jeremy, worked a treat. just had to change them both to -3 to allow for smaller postcodes
  9. Thinking about it, the word doesn't have to be split but a space added to the string before the last 3 characters
  10. ("SELECT * FROM photo WHERE id LIKE '%".$searchid."%' OR description LIKE '%".$searchid."%' OR category LIKE '%".$searchid."%' ORDER BY id") give that a try
  11. I have a string and I want to split it into 2 parts but the last part has to be 3 characters. What it is, is a uk postcode which is in the database like CF127HG and i want to format the output like CF12 7HG How do i do that?
  12. Is there a way of deleting what is in the current text file or overwriting it? I have got it to write to the text file but how do I update what's in there or can't it be done?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.