• 0

Run 4 Queries on 3 Databases in PHP


Question

Hey Guys wassup?

I basically need to run a count query on three separate databases with four queries then display the information, this is what I have, but it clearly doesn't work. Can somebody help me?

<?php
error_reporting(-1);
ini_set('display_errors', true);

$con = mysqli_connect(
  '192.168.20.10',
  'root2',
  'password',
  'contaque'
);

$con2 = mysqli_connect(
  '192.168.20.222',
  'root2',
  'password',
  'contaque'
);

$con3 = mysqli_connect(
  '192.168.20.8',
  'root2',
  'password',
  'contaque'
);

$res = mysqli_query(
	$con,
		"SELECT COUNT(*) AS 'total1' FROM contaque_hopper WHERE campaign_id = 'CLOSERUK';"
);

$res = mysqli_query(
	$con2,
		"SELECT COUNT(*) AS 'total2' FROM contaque_hopper WHERE campaign_id = 'CLOSERTC';"
);

$res = mysqli_query(
	$con2,
		"SELECT COUNT(*) AS 'total3' FROM contaque_hopper WHERE campaign_id = 'CLOSERST';"
);

$res = mysqli_query(
	$con3,
		"SELECT COUNT(*) AS 'total4' FROM contaque_hopper WHERE campaign_id = 'CLOSER3C';"
);

echo 'Buffer Status 2H: ', (int)$row['total1'], ' records.';
echo 'Buffer Status TC: ', (int)$row['total2'], ' records.';
echo 'Buffer Status ST: ', (int)$row['total3'], ' records.';
echo 'Buffer Status 3C: ', (int)$row['total4'], ' records.';

?>

Any ideas people?

Link to comment
Share on other sites

22 answers to this question

Recommended Posts

  • 0

try


<?php
error_reporting(-1);
ini_set('display_errors', true);

$con = new mysqli(
  '192.168.20.10',
  'root2',
  'password',
  'contaque'
);

$con2 = new mysqli(
  '192.168.20.222',
  'root2',
  'password',
  'contaque'
);

$con3 = new mysqli(
  '192.168.20.8',
  'root2',
  'password',
  'contaque'
);

$totals = array();
$res = $con->query("SELECT COUNT(*) AS 'total1' FROM contaque_hopper WHERE campaign_id = 'CLOSERUK';");
$row = $res->fetch_assoc();
$totals['query_1'] = $row['total1'];
$res = $con->query("SELECT COUNT(*) AS 'total2' FROM contaque_hopper WHERE campaign_id = 'CLOSERTC';");
$row = $res->fetch_assoc();
$totals['query_2'] = $row['total2'];

$res = $con2->query("SELECT COUNT(*) AS 'total3' FROM contaque_hopper WHERE campaign_id = 'CLOSERST';");
$row = $res->fetch_assoc();
$totals['query_3'] = $row['total3'];

$res = $con3->query("SELECT COUNT(*) AS 'total4' FROM contaque_hopper WHERE campaign_id = 'CLOSER3C';");
$row = $res->fetch_assoc();
$totals['query_4'] = $row['total4'];

echo 'Buffer Status 2H: ', (int)$totals['query_1'], ' records.';
echo 'Buffer Status TC: ', (int)$totals['query_2'], ' records.';
echo 'Buffer Status ST: ', (int)$totals['query_3'], ' records.';
echo 'Buffer Status 3C: ', (int)$totals['query_4'], ' records.';

Link to comment
Share on other sites

  • 0

@Dave,

Thats basically perfect, only issue is:

Buffer Status 2H: 2916 records. 
Buffer Status TC: 0 records. 
Buffer Status ST: 1000 records. 
Buffer Status 3C: 1287 records. 

TC is showing 0 records despite being on 1300 Records, any obvious work around to that issue? Open an additional connection?

Strike my last, fixed it myself :)

Thankyou so much Dave.

Link to comment
Share on other sites

  • 0

yepp. try that.

He fixes one of your problems there, when you initialized $res, you did so several times before extracting the values from the query. When you again initialized $res, you told PHP to discard the previous value, and replace it with the second query, and so forth. At the en, $res was only representing the last query.

And then, you wrote echo $totals[value] , but you hadn't set $totals to represent any value. -Dave-'s example will show you that, as he extracts the value from the result set, and gives $totals the value mysql returns.

If you want to gather all the mysql_queries before the extraction, you need to give them different variable names, so that they do not owerrite. e.g. $res1, $res2.

You could also write a method to to the same job: countCampaign($campaignname) or something like that

Link to comment
Share on other sites

  • 0

And I'm back to be more annoying, how would one get those values to refresh every... 5 seconds or so? Without doing a meta refresh?

<meta http-equiv="refresh" content="600">

Thanks guys :)

Link to comment
Share on other sites

  • 0

Hey again guys,

How would I go about adding Query 1 to the title? So far i've got this:

<?php $title = '$totals['query_1']; ?>

and

<title><?php echo $title; ?></title>

But it doesn't work. Any ideas?

Link to comment
Share on other sites

  • 0

Hey again guys,

How would I go about adding Query 1 to the title? So far i've got this:

<?php $title = '$totals['query_1']; ?>

and

<title><?php echo $title; ?></title>

But it doesn't work. Any ideas?

well, there's a typo in your first bit :

<?php $title = $totals['query_1']; ?>

Link to comment
Share on other sites

  • 0

Sweet, that worked, *facepalm*. Told you guys I sucked at this stuff.

So what about if I wanted to put VALUE of Query 1 - Query 2 - Query 3 - Query 4?

EDIT:

So I ended up going oldschool and slamming this together:

<?php $title1 = $totals['query_1']; ?><?php $title2 = $totals['query_2']; ?><?php $title3 = $totals['query_3']; ?><?php $title4 = $totals['query_4']; ?>
<title><?php echo $title1; ?> - <?php echo $title2; ?> - <?php echo $title3; ?> - <?php echo $title4; ?></title>

I'd like to clean the code up a little though, any idea how to integrate it a little cleaner?

Link to comment
Share on other sites

  • 0

Hey,

You don't need the ?><?php tags between each command. You can just have one which covers the whole area of code.

Joe

Edit

Just saw the second part, This code should work for that.

&lt;title&gt;&lt;?php echo $title1 ." - ". $title2 ." - ". $title3 ." - ". $title4; ?&gt;&lt;/title&gt;

Link to comment
Share on other sites

  • 0

Nice one, so if I was do to the title kinda like this:

&lt;title&gt;2H:&lt;?php echo $title1; ?&gt; - TC:&lt;?php echo $title2; ?&gt; - ST:&lt;?php echo $title3; ?&gt; - 3C:&lt;?php echo $title4; ?&gt;&lt;/title&gt;

I'd actually be:

&lt;title&gt;&lt;?php echo ."2H". $title1 ." - TC:". $title2 ." - ST:". $title3 ." - 3C:". $title4; ?&gt;&lt;/title&gt;

?

And the above line would be:

&lt;?php $title1 = $totals['query_1']; $title2 = $totals['query_2']; $title3 = $totals['query_3']; $title4 = $totals['query_4']; ?&gt;

Right?

Link to comment
Share on other sites

  • 0

Awesome, now. Biggest task of all:

This is how it looks right now:

script.png

What I need is a way for when query_1 drops below 400 but is above 10 an alert pops up. The reason being is occasionally we reset a dataset which brings it down to 0 for around 20 seconds so we don't need notification for every data reset we do, however if somebody hasn't been monitoring and the amount of data drops below 400 we need to upload fresh data.

How would I do this?

Link to comment
Share on other sites

  • 0

A simple if statement would do that for you, something along the lines of;

if($title1&lt;400 and $title1 &gt;10){
   #What ever you want the code to do
   #Maybe just a large red box could appear with a message in it or
   # perhaps using Jquery you could do something a bit more fancy
}

Link to comment
Share on other sites

  • 0

Awesome dude, so how could I put a regular 'popup' on that.

Something like

if($title1&lt;400 and $title1 &gt;10){
popupbox="WOAH!!"
{

?

Link to comment
Share on other sites

  • 0

Imma try:

if($title1<400 and $title1 >10){

<script type="text/javascript">

function show_alert()

{

alert("Buffer is low");

}

</script>

}

Is the Syntax for that correct?

EDIT: Does not work :(

Link to comment
Share on other sites

  • 0

The reason that is not working is because JavaScript needs to be echoed to the client as it needs to be in the HTML.

So

&lt;?php
if($title1&lt;400 and $title1 &gt;10){
   echo "&lt;script type=\"text/javascript\"&gt;";
   echo "function show_alert() {";
      echo "alert(\"Buffer is low\");";
   echo "}";
   echo "&lt;/script&gt;";
}
?&gt;

Two things I will point out so you know for the future are;

The line <script type="text/javascript"> has become <script type=\"text/javascript\">this is because the " characters will confuse the PHP interpreter so using the \ escapes them so it does nor process them as part of the code.

Secondly, in order for that to run you will need to call show_alert() when the page loads, however I am not sure how you would go about doing this but I'm sure it should be easy enough to find on Google.

Edit:

Google returned this: http://javascript.about.com/library/bltut31.htm which seems to be relevant

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.