[Ordering by a specific value without using a filesort.]

Well, I'm just trying to get rid of the filesort.

[Ordering by a specific value without using a filesort.]

Oh, ok. That sorts things out (pardon the pun).

 

How about using multiple queries (or joins) instead? If I wanted to get all of the done = 0 first, then all others and have them all sorted by posted DESC, how would I do that?

[Ordering by a specific value without using a filesort.]

This problem has been nagging me for a while now. I have multiple queries that contain this at the end:

 

ORDER BY done = 0 DESC, posted DESC

 

Which makes the results ordered in the form 0, 1, -1 which is fine. Now, because of the = 0, MySQL has to perform a filesort. Is there any way around this?

 

Also, is there any way to order the rows by 'done' = 0 first, but then disregard the 'done' state and just sort by posted?

[Getting rid of a filesort.]

It says there are only 11 rows total -- is that true?

 

Indeed, and now I've just realised why it wanted to use a filesort instead. It was actually more efficient than using the index. I added another 100 rows and now it works perfectly.

 

Thanks for your help anyway guys.

[Getting rid of a filesort.]

Well, I need to select everything because I want the 50 most recent rows from that table.

 

EDIT: Funny thing is:

 

SELECT * FROM  `logs` FORCE INDEX ( DATETIME ) ORDER BY  `datetime` DESC LIMIT 0 , 50

 

Outputs:

 

id	select_type	table	type	possible_keys	key		key_len	ref	rows	Extra
1	SIMPLE		logs	index	NULL		datetime	8	NULL	11	 

[Getting rid of a filesort.]

Thanks for your reply. That article is interesting, and I've tried it but it doesn't seem to work...

 

Here's the ammended query:

 

SELECT * FROM `logs` WHERE `datetime` > '1970-01-01 00:00:00' ORDER BY `datetime` DESC LIMIT 0, 50

 

And this results in:

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE		logs	ALL	datetime	NULL	NULL	NULL	11	Using where; Using filesort

 

I'll give the join idea a whirl soon, but as far as I can see, that should have worked...

[Getting rid of a filesort.]

I'm trying to optimise some queries that seem to be using temporary tables and filesorts, and stumbled across this nasty one. I have the following query:

 

SELECT * FROM `logs` ORDER BY `datetime` DESC LIMIT 0, 50

 

Here's the table structure:

 

CREATE TABLE IF NOT EXISTS `logs` (
  `id` mediumint(12) unsigned NOT NULL AUTO_INCREMENT,
  `userid` mediumint(10) unsigned NOT NULL,
  `objectid` mediumint(10) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  `action` varchar(100) NOT NULL,
  `reason` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datetime` (`datetime`)
) ENGINE=MyISAM ;

 

Now, by my understanding, that query should use the datetime key. But instead, it decides to do a filesort.

 

id	select_type	table		type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE		logs		ALL	NULL		NULL	NULL	NULL	11	Using filesort

 

What am I doing wrong?

[nowdoc parsing problem]

Got a better solution after twiddling with one of my Twitter regular expressions.

 

<?php

$vara = <<<'END'
my first name is $first_name
END;

$first_name = "james";

preg_match_all("/ +\\$(.*)?/i", $vara, $matches);
foreach($matches[1] as $match) {
    $vara = str_replace("$" . $match, $$match, $vara);
}

echo $vara;

?>

 

With that, you literally just define all of your variables as you do at the moment.

[nowdoc parsing problem]

I imagine a simple str_replace would suffice.

 

<?php

$vara = <<<'END'
my first name is $first_name
END;

$variables['first_name'] = "james";

foreach($variables as $name => $value) {
    $vara = str_replace("$".$name, $value, $vara);
}

echo $vara; // Output: my first name is james

?>

 

That's the best I can come up with. You may be able to use regular expressions to pick the variable names out of the nowdoc after including the defining file and then replace them with the actual variable names.

[nowdoc parsing problem]

As I recall you need to enclose variables in {}, like so:

 

$var1 = <<<FIRST
       the first name is {$varb}
FIRST;

Edit: After some manual reading, you need to use a heredoc instead of a nowdoc. http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.nowdoc

 

That says that PHP doesn't parse anything inside a nowdoc, which is defined using single quotes around your name (in this case, FIRST). By removing these quotes everything works as it should.

[imagick with php: upset by other code]

This line:

 

header( "Content-Type: image/jpeg" );

Is setting the header to indicate a jpeg image. You have a few problems with the appended script that you showed us - firstly you're setting the same header multiple times. That's just bad.

 

Secondly, you're setting the header of the PHP script (+ output) itself, which means that any web browser will interpret your page as an image. What you should be doing is referencing your standalone script using a basic image tag. Example:

 

<img src="standalone.php?file=<?php echo $file[0]; ?>" />

 

And then in the standalone.php:

 

<?php 
$file = $_GET['file']; // You'll want to sanitize this, seriously. It's a huge security hole if you don't.
$im = new imagick($file);
$im->setImageFormat( "jpg" );
header( "Content-Type: image/jpeg" );
$im->thumbnailImage(400, 0);
echo $im;
?>

[Working with preg_replace]

Could you not just use str_ireplace?

 

$pattern=array(".pdf","_","sop","mgmt","ste","risk","eng","dem","bus_it_requests","bus");
$replace=array(""," ","SOP","Management","Steady State -","Risk Management -","Engineering -","Demand Management -","Business/IT Requests","Business Management -");
echo "<a href=\"$file\">" . ucwords(str_ireplace($pattern,$replace,$file)) . "</a></br>";

Just a thought - it depends on how str_replace works as to whether it'll solve your issue. If you need some case sensitive, then just do the case insensitive one and then the case sensitive one on the result.

[Mass check Google site]

My bad - replace:

 

    foreach($links as $link) {
        echo checkSite($_POST['www']) . "<br />";
    }

With:

 

    foreach($links as $link) {
        echo checkSite($link) . "<br />";
    }

[Mass check Google site]

<?php

function checkSite($www) {
  $ch = curl_init('http://www.google.pl/search?hl=pl&q=site%3A'.trim($www).'&btnG=Szukaj&source=hp');
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  $sHtml = curl_exec($ch);
  preg_match('#\<div id=resultStats\>.*([0-9,]+) wynik#Ui', $sHtml, $aMatches);
  curl_close($ch);
  return (int)str_replace(',', '', $aMatches[1]);
}

if($_POST['www']) {
    $links = explode("\n", $_POST['www']);
    foreach($links as $link) {
        echo checkSite($_POST['www']) . "<br />";
    }
} else {
    print ('<form method="POST"><textarea name="www" rows="20" cols="100"></textarea><br /><br /><input type="submit" value="Submit" name="B1"> <input type="reset" value="Reset" name="B2"></form>');
}

?>

[Admin Login Problems]

The only thing that could be not working would have to be the MySQL connection - everything else is fine. I even tested the preg_replace output on the username and password but that seems to be working alright.

 

Try echoing $existCount to see how many rows it's matching, and also add or die(mysql_error()); on the query section.

[Connect to MYSQL Database]

As PFMaBiSmAd said just after my post, you're switching the database name and your username around. Your code should be:

 

// Place db host name. Sometimes "localhost" but  
// sometimes looks like this: >>      ???mysql??.someserver.net 
$db_host = "localhost"; 
// Place the username for the MySQL database here 
$db_username = "esolarch_admin2";  
// Place the password for the MySQL database here 
$db_pass = "Password";  
// Place the name for the MySQL database here 
$db_name = "esolarch_ms";

[how to sort a feed]

If you wanted more than one piece of information, then use a multi-dimensional array. Change:

 

$array[] = $item->title;

To:

 

$array[] = array(
    "title" => $item->title,
    "link" => $item->link 
);

[how to sort a feed]

$mattal999 : rsort() sorts descending alphabetically...not the same as outputting in reverse order.

 

I was just going with the function that was originally suggested in the main post. I've updated my post to use array_reverse.

[how to sort a feed]

You'd need to first put all of the data into an array, and then sort it using that function.

 

$array = array();

foreach ($xml->channel->item as $item) { 
    $array[] = $item->title;
}

$array = array_reverse($array);

foreach($array as $item) {
    echo $item . "<br />";
}

Edit: Crayon always beats me to it...

[Connect to MYSQL Database]

Well, this part of the error:

 

'esolarch_databas'@'localhost'

 

Says that MySQL is trying to connect to localhost using the username esolarch_databas (note the missing 'e'). I think you may be using too many characters for the username, hence either PHP or MySQL is cutting off the extra characters. Try renaming your username to something short and try again.

[php $_POST[' ']]

Don't undefined index notices mean that you didn't check if the variable has a value before using it?

 

if(isset($_POST['rim'.$x])) {
    $rim[] = $_POST['rim'.$x];
}

[what's wrong with this join query?]

It would have to be:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' ORDER BY products.category DESC LIMIT 6");

[what's wrong with this join query?]

This might be slightly off topic now, but the problem with your original query was due to a rogue DESC. The original:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6");

The fixed:

 

$sql = mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' LIMIT 6");

[I am new to PHP and trying to debug some old code. How am I to reade the follow]

Ah, didn't know that - thanks!

[I am new to PHP and trying to debug some old code. How am I to reade the follow]

Well, $GLOBALS is an array (that I assume is user defined).

 

You're correct in your theory that the variable $properties is assigned a value, but that value is actually a value from an array.

 

I'm assuming that you know what an array is. If not, then you have some reading to do.

 

Basically, $GLOBALS['Props'] would retrieve the value from the array associated with the key Props.