PHP Coding

Started by Supertanker, 07 January 2012, 04:33:41

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Supertanker

Hey all;

If you guys are going to be writing in PHP, I can throw in my experience if you need it. I'm by no means a great coder but I've been working on several PHP applications over the past two years and have been developing a staff application for one of the local colleges (just passed the two year mark--no crashes or failures of my code yet!), so if nothing else I can help you find bugs. :)


Puritan

That's great  ;)


I'll bare that in mind.
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Puritan

#2
Ok, Supertanker  :)


Can you find a way to insert Todays date( MM-DD) into this code?
As you can see I'm using '_' as wildcard for year (YYYY)


<?php $con =

mysql_connect("myhost","database","password");
if (!$con) { die('Could not connect: ' .

mysql_error()); } mysql_select_db("database", $con);
$result = mysql_query("SELECT * FROM map_collection

WHERE date like '____-MM-DD' ORDER BY column DESC");

while($row = mysql_fetch_array($result))

{ echo ' '.$row['ID'];
echo '<font size=1><br/> Map Name: '.$row['title'];
echo '<br/> Author: '.$row['author'];
echo '<br/> Released: '.$row['date'];
echo '<br/> Score: '.$row['score'];
echo '<br/> Download: '.$row['link'];
echo '<br/> Screenshot: <br><br>'.$row['thumb'];
echo '<br/><br/>'; } ?>
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Supertanker

#3
There's several ways we could do it. Inefficient way (about as inefficient as LIKE):

select * from map collection where extract(MONTH from now()) = extract(MONTH from date) AND extract(DAY from now()) = extract(DAY from date);

A more efficient way would be to store the month and day as separate integers in separate columns. What's a few more bytes of DB space per row if it nets us a database that doesn't strain the server as much? Using LIKE or function calls will get slow as the database gets bigger. If you wanted to have features like searching on an interval between dates, then stick in the date or datetime as an extra column--it will use a little extra space, but for searching it would make things easier.

So your table heading might look like:

| id (int) | map_name (varchar 255) | description (text) | release_date (date or datetime) | release_month (int) | release_day (int) | release_year (int) | rating (single?) | filename (varchar 255) | screenshots (varchar(1024) | ...

Make an index(month, day) on table creation for great justice and speed. (Not like Polymer.)

Now you can do queries like:

select *, day(now()) as day, month(now()) as month from map_collection where release_month = month and release_day = day limit 5;

At least, I think. :> Indexes should make it faster but even without them it's cutting down function calls exponentially.

You can use release_date and mysql's date functions if you want to offer a search, say, of all maps between January 9th, 1999 and September 2nd, 2003; release_year would be useful to have a quick glance of what's been released all year without fiddling with dates.

Edit again: inserting would be easy too (but be sure to use your escaping functions). You could either compute the day and month number in PHP or let MySQL do it for you with its functions MONTH(), DAY(), and YEAR() on the incoming timestamp, which may have to be formatted with PHP's date() first.

Puritan

#4
Ehh... I want the present date + day into the query. No intervals. Not for searching purposes. There are more than enough searching features on the search page.


It's more like a code that finds todays month + day and insert those two results into the code I provided from above. ( Is this where a "datetime" column comes to rescue? Or does the server 'know' what the present date is by default?)
I have the rest of the code and a output page set smooth as guacamole  :D


Ps: I already have the "date" column in the database as you can see from the code( WHERE date ...)
And the feature I'm looking for wont slow down the function.
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Supertanker

#5
On my mockup server:


mysql> select right(curdate(), 6);
+---------------------+
| right(curdate(), 6) |
+---------------------+
| -01-07              |
+---------------------+
1 row in set (0.00 sec)


From there:

mysql> select * from map_collection where date LIKE concat('%', right(curdate(), 6));
+----+------------+
| id | date       |
+----+------------+
|  1 | 2012-01-07 |
|  2 | 2011-01-07 |
+----+------------+
2 rows in set, 1 warning (0.00 sec)


I was curious about the warning; it seems safe to ignore:


mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '%-01-07' for column 'date' at row 1 |
+---------+------+------------------------------------------------------------+


Seems like that might fit the bill. You could also build the string in PHP by using date(). (Don't have php in front of me atm but I imagine it'd be something like this):


$wildcard = date('%-m-d');


Then your query might look like: where date LIKE $wildcard;

Gah, sorry, edits. Getting distracted trying to do tech support for family at the same time.

Edit again:
a DATE column stores a date in the form yyyy-mm-dd.
a DATETIME column stores a date and a time in the form yyyy-mm-dd hh:mm:ss

Supertanker

mysql> select * from map_collection where cast(date as char) LIKE concat('%', right(curdate(), 6));

This will get rid of that annoying warning. It's apparently a MySQL bug: http://bugs.mysql.com/bug.php?id=38915 (never got fixed though).

Puritan

#7
You are my man, Supertanker!! It works with the code in the second post above. I didn't receive any warnings either.
I'll give you credit when the time is right.


Thank you very much  :)


Edit: Your last suggestion worked as well. I'll better use that one in order to avoid errors and warnings then  ;)
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Supertanker

Heh, my MySQL version may be a little out-of-date then. :)

Glad it worked!

Puritan

#9
Need some help with this.
The script works fine in PHP 5.6

I receive a "fatal error" when executing the same script when the server is set to PHP 7.

The error points at this part of the script:

$sql = sprintf("SELECT UNIX_TIMESTAMP(last_access) last_time FROM downloaded WHERE filepath = '%s' AND ipadres = '%s' ORDER BY last_access DESC", $path, $ip);
$res = mysql_query($sql);
if (mysql_num_rows($res) > 0) {
   $last_xs = mysql_result($res, 0, 'last_time')+2592000;
   if ($last_xs < time()) {
      mysql_query(sprintf("REPLACE downloaded SET filepath = '%s', ipadres = '%s', last_access = NOW()", $path, $ip));
      $dl = true;

I've tried to edit some of the code into MySQLi but it seems that I'm not coding it correctly.
Any suggestions?
Maybe someone can write this snippet in MySQLi the right way?
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Forge

Take it down to the beach with a hammer and pound sand up your ass

Puritan

#11
Quote from: Forge on  09 August 2018, 20:46:57
https://secure.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated

https://secure.php.net/manual/en/migration70.php

https://secure.php.net/manual/en/book.mysqli.php


Thanks
I'm aware of those sites but still...
The funny part is that when I'm uploading the script to an online MySQLi validator it returns with "OK - no errors".
But when I'm setting the server to PHP 7 and try a download the server returns a fatal error ( and pointing to the snippet above).


Would be nice if somebody out there could re-write the snippet in MySQLi  :)
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Forge

#12
https://phpcodechecker.com/

I didn't get invested in php and mysql.

The site I was working with, I got booted from, so I only have the basics down. Most of it was pre-canned stuff anyway.
(paranoid delusions. she accused me of holding the site hostage by threatening to allow hackers to take it over. All over re-arranging a couple hundred stupid smilies that were causing the site to lag for some browsers)

again. just the basics
like mysql_query($sql) would be mysqli_query( $link, $query)

https://www.phpclasses.org/blog/package/9199/post/3-Smoothly-Migrate-your-PHP-Code-using-the-Old-MySQL-extension-to-MySQLi.html
https://dzone.com/articles/convert-mysql-to-mysqli

somewhere in your code you're probably going to have to change mysql_select_db to mysqli_connect or mysqli_select_db
Take it down to the beach with a hammer and pound sand up your ass

Puritan

#13
Thanks a lot Scott, for the link to phpclasses  :)
The guy pushed me in the right direction it seems.

The script is now working.

I edited this part:

$last_xs = mysql_result($res, 0, 'last_time')+2592000;
   if ($last_xs < time())


into:

mysqli_data_seek($result, 0);
if( !empty($last_time) ) {
  while($finfo = mysqli_fetch_field( $result )) {
    if( $last_time == $finfo->name ) {
      $f = mysqli_fetch_assoc( $result );
      $fetch =  $f[ $last_time ]+2592000;
    }
  }
} else {
  $f = mysqli_fetch_array( $result );
  $fetch = $f[0];
}
   if ($f < time())
Buried under dirt, a diamond in the mud
Infinity is waiting there 'cause nobody can burn a glass cathedral

Forge

#14
and all that extra code is somehow supposed to make everything more secure.
okay.

I just never got into going too far beyond the basic php and sql, so my knowledge is pretty entry level.
Problem is, I was getting into it, then I got banned from the site I was helping to build, so I quit learning.
I figured if I was never going to use it, then what's the point.

sorry I couldn't be of more help. Glad you found the answer.
Take it down to the beach with a hammer and pound sand up your ass