Hooker

Thanks so much!

Hi, I've made 4 tables like so (excuse messy coding, it's all just a mock up atm): CREATE TABLE IF NOT EXISTS `customer` ( `card_no` varchar(20) NOT NULL, `title` varchar(5) NOT NULL, `firstname` varchar(42) NOT NULL, `lastname` varchar(42) NOT NULL, `address` varchar(100) NOT NULL, `postcode` varchar(10) NOT NULL, `LAT` varchar(20) NOT NULL, `LANG` varchar(20) NOT NULL, `phone` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`card_no`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `deliveries` ( `card_no` varchar(64) NOT NULL, `window_UID` int(14) NOT NULL, `Bags` int(2) NOT NULL, `Checked` tinyint(1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `drivers` ( `uid` int(4) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; CREATE TABLE IF NOT EXISTS `windows` ( `UID` int(10) NOT NULL AUTO_INCREMENT, `driver` int(4) NOT NULL, `area` int(1) NOT NULL, `window_start` varchar(14) NOT NULL, `window_end` varchar(14) NOT NULL, `date` varchar(14) NOT NULL, PRIMARY KEY (`UID`), UNIQUE KEY `driver` (`driver`,`window_start`,`window_end`,`date`), KEY `Date` (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; So we can keep track of deliveries a little easier (this is a pet project, at the moment everythings done on paper and it's annoying me), i'm pulling the data out of the database like so: <?php $host="xxx"; $username="xxx"; $password="xxx"; $db_name="xxx"; $today = date("d.m.y"); mysql_connect("$host", "$username", "$password")or die("cannot connect to db"); mysql_select_db("$db_name")or die("cannot select DB"); $sql=("SELECT deliveries.bags, deliveries.checked, windows.area, windows.window_start, windows.window_end, windows.date, customer.title, customer.firstname, customer.lastname, customer.address, customer.postcode, customer.phone, drivers.name FROM deliveries LEFT JOIN customer ON deliveries.card_no = customer.card_no LEFT JOIN windows ON deliveries.window_UID = windows.UID LEFT JOIN drivers ON windows.driver = drivers.uid WHERE windows.date = '$today' ORDER BY windows.UID, deliveries.card_no, deliveries.bags"); $result=mysql_query($sql); $count=mysql_num_rows($result); if($count>=1){ echo"<table width=\"40%\" border=\"0\" cellspacing=\"4\">"; while($row = mysql_fetch_array($result)){ echo "<tr> <td colspan=\"5\"><b>Driver: " . $row['name'] . " * Area: " . $row['area'] . " * Window: " . $row['window_start'] . " - " . $row['window_end'] . "</b></td> </tr>"; echo("<tr> <td>1. " . $row['title'] . " " . $row['firstname'] . " " . $row['lastname'] . "</td> <td>" . $row['address'] . ", " . $row['postcode'] . "</td> <td>" . $row['phone'] . "</td> <td>Bags: " . $row['bags'] . "</td> <td>Checked: "); echo $row['checked']? 'Yes' : 'No'; echo("</td> </tr>"); } echo"</table>"; } else { echo"<br>No windows found."; echo date("d.m.y"); } ?> The plan is to get an overview of the days deliveries through the day as deliveries are added and have them in separate tables but at the moment the best i can do is list them with repeating headers, can anyone help me out with a simple way to list them like so: Delivery Window 1 delivery 1 delivery 2 delivery 3 etc Delivery Window 2 delivery 1 delivery 2 delivery 3 Delivery Window 3 delivery 1 delivery 2 delivery 3 Any help is more than appreciated, like i said for now it's a proof of concept mock up and everything is up for being changed/improved once i have something functional

<?php $firstTime=strtotime("2012-07-13 14:00:00"); $lastTime=strtotime("2012-07-13 20:00:00"); $time=$firstTime; while ($time < $lastTime) { echo date('H:i:s', $time) . " - "; $time = strtotime('+2 hours', $time); echo date('H:i:s', $time) . "<br>"; } ?>

Hello, I'm having a little trouble deciding how to go about splitting an amount of time into intervals, i wan't to be able to set in a start and end time and split them down into 2 hour chunks so: Start: 14:00 End: 24:00 Intervals: 14:00 - 16:00, 16:00 - 18:00, 18:00 - 20:00, 20:00 - 22:00, 22:00 - 24:00 ready to be logged into a mysql database. Can anyone set me in the right direction? Thanks!

Hello Everyone, I run a site here: http://cerbcorp.com using SMF and Simpleportal but the landing page doesn't display right, there's supposed to be an image slider at the top of the page that doesn't always display. It works if i go to http://cerbcorp.com/index.php but not if the /index.php isn't there, is there anyway to use htaccess to automatically redirect http://cerbcorp.com to http://cerbcorp.com/index.php (but not sub forums or ?action='s)? i tried using: Options +FollowSymlinks RewriteEngine On RewriteRule ^$ /index.php [R=301,L] RewriteCond %{SCRIPT_FILENAME} !-f RewriteCond %{SCRIPT_FILENAME} !-d RewriteRule . /index.php [QSA,L] But it seems to redirect all links back to http://cerbcorp.com/index.php

sorry about my initial reply, so freaking hot at the moment x.x thanks for the help, i think its got me in the right direction.

Actualy its the "PHP Help" forum and i had a legitimate question about how to do something in PHP/MySQL after searching online for a while, trying to find a method to do it. I wasn't asking for anyone to do it for me, if thats how you took it then i'm sorry it wasn't worded so that you could understand it but it was never my intention to get readymade code. If you didn't want to help/were unable to help, fine, just don't reply.

I was just wondering if anyone could tell me how to do this: - Have a normal HTML dropdown box like this: January Febuary March April etc but when the page loads, it grabs a value from a table and selects that value in the dropdown box, so say someone previously selected "March" and now they've gone back to the form to change it to January, march will already be selected. Thanks for any and all help in advance!

I'm trying to write a banner sharing script that works on a PHPBB forum, i don't have access to the actual forum source so it needs to totaly be external. Right now i have it so people get code similar to this: [url="www.mysite.com/refer.php?usr=1234"][img=www.mysite.com/banner.php?usr=1234][url] And i'm trying to track the click throughs, ie. how many clicks a particular banner has and who from so at the moment i'm doing this - In the image generation (banner.php searches a db table for a random image and shows it) it grabs the user id and assigns the image its showing to the user (in "ActiveBanner") so the table looks like this: UID | Name | Points | ActiveBanner Then when someone clicks through it adds 1 point to the user and removes 1 point from the user that the banner is coming from before redirecting the clicker to the right page. I just wonderd if there was any better way to track the click throughs or if anyone can suggest any improvements. Thanks in advance

bump

also, if i have something like: <a href="www.website.com/link.php?id=12345"><img src="http://www.website.com/pic.php" border="0"></a> Or [url="www.website.com/link.php?id=12345"][img=http://www.website.com/pic.php][/url] And i grabbed the image out of a db to generate "pic.php" is there anyway to get the ID of the pic generated, in the link like "link.php?id=12345&pic=123"

its just for a link exchange script for a friend, no text added, just need a random image chosen from a database and shown on forums and such that can track the clicks.

why kinda stress does it cause to the server with this method/are there any better methods of doing this?

Hey, I have a MySQL db table which looks like this: ID | Name | ImageURL | XSize | YSize I wonderd if anyone could explain or point me in the right direction of a tutorial where i could take that information and generate an image based on the dimensions in the table and the image held at the url in the "ImageURL" field and be able to include it on a page like this: <img src="http://www.website.com/pic.php"> Any help would be greatly appreciated, Chris

I went for this instead and it returns the right information: SELECT tname AS TEAM, Sum( P ) AS P, Sum( W ) AS W, Sum( D ) AS D, Sum( L ) AS L, Sum( Q ) AS Q, SUM( F ) AS F, SUM( A ) AS A, SUM( F ) - SUM( A ) AS GD, SUM( Pts ) AS PTS FROM ( SELECT hteam Team, 1P, IF( hscore > ascore, 1, 0 ) W, IF( hscore = ascore AND hscore <> '0', 1, 0 ) D, IF( hscore = ascore AND hscore = '0', 1, 0 ) Q, IF( hscore < ascore, 1, 0 ) L, hscore F, ascore A, CASE WHEN hscore > ascore THEN 6 WHEN hscore = ascore AND hscore <> '0' THEN 3 WHEN hscore = ascore AND hscore = '0' THEN 1 ELSE 0 END PTS FROM results UNION ALL SELECT ateam, 1, IF( hscore < ascore, 1, 0 ) , IF( hscore = ascore AND ascore <> '0', 1, 0 ) , IF( hscore = ascore AND ascore = '0', 1, 0 ) , IF( hscore > ascore, 1, 0 ) , ascore, hscore, CASE WHEN hscore < ascore THEN 6 WHEN hscore = ascore AND ascore <> '0' THEN 3 WHEN hscore = ascore AND ascore = '0' THEN 1 ELSE 0 END FROM results ) AS tot, team WHERE tot.Team = team.uid GROUP BY Team ORDER BY SUM( Pts ) DESC , GD DESC ; LIMIT 0 , 30 Anyone have any suggestions to improve the query? Any help is realyl appreciated!

It seems to only get the first row returned right.

Top part of the union generates: Bottom part of the union generates: so the answer is.. both?

not sure exactly what you mean sorry

Okay, so this is where i'm at now: Tables: CREATE TABLE IF NOT EXISTS team ( uid int(11) unsigned NOT NULL auto_increment, tname varchar(100) NOT NULL, PRIMARY KEY (uid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE results ( uid int(11) unsigned NOT NULL auto_increment, league varchar(1) NOT NULL, hteam int(11) unsigned NOT NULL, hscore int(11) unsigned NOT NULL, ateam int(11) unsigned NOT NULL, ascore int(11) unsigned NOT NULL, `date` date NOT NULL default '0000-00-00', `status` tinyint(1) unsigned NOT NULL, PRIMARY KEY (uid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query: SELECT tname, Sum(P),Sum(W),Sum(D),Sum(L),Sum(Q),SUM(F),SUM(A),SUM(GD),SUM(Pts) FROM(SELECT hteam Team, 1 P, IF (hscore > ascore,1,0) W, IF (hscore = ascore AND hscore <> '0',1,0) D, IF (hscore = ascore AND hscore = '0',1,0)Q, IF (hscore < ascore,1,0) L, hscore F, ascore A, hscore-ascore GD, CASE WHEN hscore > ascore THEN 6 WHEN hscore = ascore AND hscore <> '0' THEN 3 WHEN hscore = ascore AND hscore = '0' THEN 1 ELSE 0 END PTS FROM results UNION ALL SELECT ateam, 1, IF (hscore < ascore,1,0), IF (hscore = ascore AND ascore <> '0',1,0), IF (hscore = ascore AND ascore = '0',1,0), IF (hscore > ascore,1,0), ascore, hscore, ascore-hscore GD, CASE WHEN hscore < ascore THEN 6 WHEN hscore = ascore AND ascore <> '0' THEN 3 WHEN hscore = ascore AND ascore = '0' THEN 1 ELSE 0 END FROM results) as tot, team WHERE tot.Team=team.uid GROUP BY Team ORDER BY SUM(Pts) DESC; My only problem is this: Can anyone give me any info on why the Goal Difference isnt being calculated properly for all teams? Also any help improving the query would be greatly appreciated! P.S - "IF (hscore = ascore AND hscore = '0',1,0)Q," is for abandoned games purely because abandoned games award 1 point to each team. Thanks in advance!

Thanks for the reply, i edited the structure as corrected, what i really wanted to know is how i'd go about calculating the wins/losses/draws fields (and their points based on 0 for a loss, 1 for a draw and 3 for a win) based on the results of all their games with Mysql/PhP as there are up to 20 teams at a time in each league.

Hey Everyone, I'm building a cricket website for my local cricket league so the kids can see their stats etc and I've just about built everything but the league table and wondered if i could get a bit of help/advice on the best way to build it. At the moment the tables look like this: CREATE TABLE `Team` ( `uid` int(11) unsigned NOT NULL auto_increment, `teamname` varchar(100) NOT NULL default '', PRIMARY KEY (`uid`) ) TYPE=MyISAM; CREATE TABLE `results` ( `uid` int(11) unsigned NOT NULL auto_increment, `league1` varchar(5) NOT NULL default '', `team1` varchar(100) NOT NULL default '', `team2` varchar(100) NOT NULL default '', `team1_score` varchar(100) NOT NULL default '', `team2_score` varchar(100) NOT NULL default '3', `date` date NOT NULL default '0000-00-00', `deleted` int(1) unsigned NOT NULL, PRIMARY KEY (`uid`) ) TYPE=MyISAM; There are 3 divisions of the league (A,B,C) and what i basically want to do is grab all the data from the correct league that isn't deleted, could up how many games/wins/draws/losses/points for/points against that a team has and order it based on who has the highest points (1 point for a draw, 3 for a win, 0 for a loss) using points for - points against as a tie breaker for teams with equal points then display them like this: Team Name | Played | Win | Loss | Draw | PF | PA | Points I can't quite get my mind around the most efficient way to do this without a million and one queries and using PHP to do half of the actual leg work so any help is greatly appreciated. Thanks in advance!

Foxpro 9 supports alot of the SQL syntax, there is a connector on the mysql site.

As you can see in my attempt erlier in this thread the plan was to: - Have the user assign the start and the finnish variables (x&y split into two variables for each) - Assign a variable for the current coordinate the script is processing (again split into x&y) - Start the loop to check if the current variable is the same as the finnish variable (obviously, if its done. stop.) - Pull each coordinate within 1 of the current coordinate out of the mysql table - Start a loop to calculate each squares F value 1 by 1, if the F value is smaller than the previous, assign its coordinates to the "current coordinates" variables - Once the loop is finnished and the coordinates the the lowest F value is assigned, pass them on to the parent loop and check if it's at the end or not. on the spot i can't see a fault in the logic, perhaps someone could make suggestions to the logic or look at the code i posted erlier in this thread and point out any flaws? thanks

naw im not, i was just using it to very quickly test something lol