Posts by timmylogue

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says




>

This query is inefficient and it won’t use indexes, if any. As a rule of thumb, one should never use “NOT”, or “different from” in the comparison clauses. A more efficient version is the following:

SELECT * from TABLE WHERE registered < DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)
First of all, it has nothing to do with indexes. :)

When you use SQL, it has always to do with indexes. Trust me on that, I’ve been a DBA for long enough.


Besides, I know of famous frameworks which allows the use of NOT in comparison clause. Or <> as per SQL-92 standard

The fact that they allow it doesn’t mean that it’s an optimal way of doing things. The ”<>” clause, in particular, is a performance killer and should always be replaced by a more efficient one. You will also find dozens of frameworks that compare dates using strings (e.g. SUBSTR(DATE(field), 4) = '20150414'), and that doesn’t make it a good practice (quite the opposite).

Another example is the method, that many people use even if they should not, of running a simple SELECT * FROM table and then using foreach in PHP to go through all the results and take the ones they want. That’s a terrible idea, especially when one nests queries in foreach loops (i.e. foreach…SELECT…foreach…SELECT). In the RDBMS world it’s called RBAR approach, and it’s frown upon (for a good reason).

Ah Jeez! Alright alright.. mate… I take my words back :)

Yes, pulling all and then doing via scripting language is the worst thing to do. However, since you are a DBA, may I have an opportunity to ask… At times, we want to implement a search engine (myisam based) which returns the results grouped by – alphabet-match first.. ordered alphabetically… however, if I want to push special characters at the bottom, (during ASC on the row)... I faced this issue last month and ran it past php to push the special characters based result at the bottom… For example, if ‘alpha’ is searched… it should return… alpha, alphabet, alpha-bet and not alpa,alpha-bet, alphabet. how could we achieve that using sql-only approach?




You can also do this with PHP. $sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql);
Thank you!!

Hey!

If you could help me out with one more thing! How would I echo out the results for this? I guess I would use some loop too!

screenshot

I’m trying to display the “username(s)” of whoever has the value “expired” in their row. I have tired to Google some help before posting but I just can’t find out how to do this!

I was hoping this would work? I’m I close?

$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
}

echo $whoexpired;

Thanks :)

And you see only the last username :) That is because you are ‘echo’-ing it outside the while loop, at each loop, $whoexpired is getting re-assigned based on the number of rows.

Make that:

$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
    echo $whoexpired;
}
mysql_free_result($result);

Also, since I presume you are new and learning, dive into MySQLi rather mysql_ functions.. mysql_ functions are deprecated as of php 5.5 and will be removed for good…

Happy learning :)

Great! Thank you for all your help!

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d");

    if($datenow > $whoexpired){
        echo $user;
    }

}

This should do it, but you have to change your “trial_date”ended” field from DATETIME to DATE.

OR

If you need it as DATETIME, this should do it:


$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];
    $datenow = date("Y-m-d H:i:s");

    if($datenow > $whoexpired){
        echo $user;
    }

}

Oops! Now I’ve seen that the “trial_date_ended” is “expired” or empty.

This should do it:
$result = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result)) {

    $whoexpired = $row['trial_date_ended'];  
    $user = $row['user'];

    if($whoexpired == "expired"){
        echo $user;
    }

}

Thank you! :) Worked!

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Hey KC, Thanks for all your help! Maybe you can help me out one last time? I’m having an issue with…

(My last post) http://codecanyon.net/forums/thread/php-mysql-check-if-any-dates-have-passed/171074?page=1&message_id=1250632#1250632

Any help is appreciated!

Thanks!

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says


You can also do this with PHP. $sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql);
Thank you!!

Hey!

If you could help me out with one more thing! How would I echo out the results for this? I guess I would use some loop too!

screenshot

I’m trying to display the “username(s)” of whoever has the value “expired” in their row. I have tired to Google some help before posting but I just can’t find out how to do this!

I was hoping this would work? I’m I close?

$result = mysql_query("SELECT * FROM users WHERE 'username'");
while ($row = mysql_fetch_array($result)) 
{
    $whoexpired = $row['trial_date_ended'];  
}

echo $whoexpired;

Thanks :)

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

You can also do this with PHP. $sql = "SELECT * FROM yourtable"; $query = mysqli_query($dbconnectvariable, $sql); if($query){ while($row = mysqli_fetch_assoc($query)){ $date = $row['yourdatefield']; Inside the WHILE function, you can use $row to take each of the fields and pass them into variables, then email them to whatever email you want.

Thank you!!

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Thank you! :)

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Hey guys,

Any help is appreciated!

I need a way to display from my database if any dates (see image below)

in column “registered” have passed 30 days (expired) from today’s date and If so, I will need it to email me the whole row that expired.

Crap… I hope that made sense! haha.

Thanks!

- Tim

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Maybe someone can help me out.

When the user switches the case statement (via button) and then exits out of the web browser – I would like it to go back to the last case statement they choose when they go back in. Can this happen?

Thanks :D

var sideshowLoader:Loader = new Loader();
addChild(sideshowLoader);

function clickHandler(e:Event):void
{
    switch (e.target.name)
    {

            // button 1
            case "button1" :
            sideshowLoader.load(new URLRequest("slideshow1?"+Math.random()));
            break;

            // button 2
            case "button2" :
            sideshowLoader.load(new URLRequest("slideshow2.swf?"+Math.random()));
            break;
    }
}
328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Glad your sorted, I wasnt being fa-see-shus ( can never spell it ) , but the amount of times I run the query before connection, I need punching

haha oh, I know. I do it all the time.

328 posts
  • Has been part of the Envato Community for over 8 years
  • Has collected 100+ items on Envato Market
  • Located in United States
timmylogue
says

Here you go, this is the full code that I will use
<?php
// configuration
$dbtype        = "mysql";
$dbhost        = "localhost";
$dbname        = "something";
$dbuser        = "root";
$dbpass        = "mysql";

// database connection
...........

Cool, thanks again!

by
by
by
by
by
by