Jump to content

The Little Guy

Members
 View Profile  See their activity

  • Posts

    6,675

  • Joined

  • Last visited

 Content Type 

Posts posted by The Little Guy

    join query slowing down the server

    in PHP Coding Help

    Posted

    First, format your query properly, the way your joining is supported, but I don't think it is an optimized format (I could be wrong).

     

    SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

     

    Second, place an "EXPLAIN" in front of your "SELECT"

     

    EXPLAIN SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

     

    You "Key" column should have a key in it, if it is "NULL" that might be your problem.

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    There is also:

     

    LEFT JOIN - Returns all the results from table A, then connects the primary key from table A to the foreign key on table B, if the values are NOT in table B the fields in table B will be filled with NULL. (I believe this is the most common join)

     

    RIGHT JOIN - opposite of LEFT JOIN

    Creating an array to make a graph (Math)

    in PHP Coding Help

    Posted

    Okay, I have a database filled with data (very small sampling of data):

     

    mysql> select ping_id, domain_id, loss, average from ping_history where domain_id = 1 order by ping_id desc limit 20;
+---------+-----------+------+---------+
| ping_id | domain_id | loss | average |
+---------+-----------+------+---------+
|    8983 |         1 |    0 | 93ms    |
|    8981 |         1 |    0 | 98ms    |
|    8977 |         1 |    0 | 105ms   |
|    8974 |         1 |    0 | 93ms    |
|    8971 |         1 |    0 | 93ms    |
|    8968 |         1 |    0 | 95ms    |
|    8965 |         1 |    0 | 96ms    |
|    8961 |         1 |    0 | 93ms    |
|    8959 |         1 |    0 | 97ms    |
|    8956 |         1 |    0 | 93ms    |
|    8953 |         1 |    0 | 94ms    |
|    8950 |         1 |    0 | 95ms    |
|    8947 |         1 |    0 | 95ms    |
|    8944 |         1 |    0 | 99ms    |
|    8941 |         1 |    0 | 94ms    |
|    8938 |         1 |    0 | 98ms    |
|    8935 |         1 |    0 | 94ms    |
|    8932 |         1 |    0 | 94ms    |
|    8929 |         1 |    0 | 96ms    |
|    8927 |         1 |    0 | 96ms    |
+---------+-----------+------+---------+
20 rows in set (0.04 sec)

     

    From that data, I am using the last column that contains: 94ms, 105ms, etc.

     

    I then take that data and build a javascript array using php:

     

    <scrip type="text/javascript">
<?php
$stats = Stats();
$domain_id = (int)$_GET['domain_id'];
echo $stats->jsHourlyResponseTimes($domain_id);
?>
</script>

     

    jsHourlyResponseTimes() does a query on the database, then passes the data to a second method to build the javascript array (as seen above):

    private function buildJSResponse(){
$data = "";
$i = 0;
$arr = array();
while($this->db->row()){
	$arr[] = (int)$this->db->field("average");
}
if(empty($arr))
	return "";
$this->max = max($arr);
$this->min = min($arr);
$this->mid = round(($this->max + $this->min) / 2);
foreach($arr as $val){
	$nTime = (($val / $this->max) * 100);
	$data .= "data[$i] = ".$nTime.";";
	$i++;
}
return $data;
}

     

    when it is all complete I get an array that currently looks like this:

     

    <script type="text/javascript">
var data = new Array();
data[0] = 100;
data[1] = 87.619047619048;
data[2] = 88.571428571429;
data[3] = 89.52380952381;
data[4] = 88.571428571429;
data[5] = 89.52380952381;
// and so on....
draw(data);
</script>

     

    each key is the vertical line to place the point on (spaced depending on the number of results returned), and each value is the y position.

     

    So within that data set, say the lowest number is 5 and the highest number is 20. instead of the value being 5, or a percentage I would like it to be 100 (since my graph is 100 px tall). Instead of 20 being 20 or the percentage it should be 0.

     

    Using that, I would like results that are like so:

    <script type="text/javascript">
var data = new Array();
data[0] = 25.62; // real number is about 8
data[1] = 87.6190; // real number is about 6
data[2] = 100; // real number is 20
data[3] = 0; // real number is 5
data[4] = 13;  // real number is about 6
data[5] = 89.52380952381; // real number is about 18
// and so on....
draw(data);
</script>

     

    In each comment the "real number" is the one that came from the database, while the value is where is should be located on the graph.

     

    No number should EVER be larger than 100, and no number should EVER be smaller than 0

    date manipulation correct syntax

    in MySQL Help

    Posted

    I think this should help you.

     

    a.

    select * from my_table where date_col between now() and date_sub(now(), interval 5 day);

     

    b.

    select * from my_table where date_col < date_sub(now(), interval 5 day);

     

    c.

    select * from my_table where date_col between date_sub(now(), interval 30 day) and date_sub(now(), interval 5 day);

    help writing a (somewhat) complex query...

    in MySQL Help

    Posted

    Still not 100% sure if I get what you are looking for, so take this and run with it:

     

    $sql = mysql_query("select *, if(up.project_id is null, 0, 1) as isProject from users_users uu left join users_projects up on(uu.userid = up.userid and up.project_id = 123)");
while($row = mysql_fetch_assoc($sql)){
$projectExists = (bool)$row['isPorject'];
echo "<input type='checkbox' name='something' ".($projectExists?"checked='checked'":"")." />".$row['userid'];
}

    Creating an array to make a graph (Math)

    in PHP Coding Help

    Posted

    I have the following code:

     

    private function buildJSResponse(){
$data = "";
$i = 0;
$arr = array();
while($this->db->row()){
	$arr[] = (int)$this->db->field("average");
}
$this->max = max($arr);
$this->min = min($arr);
$this->mid = round(($this->max + $this->min) / 2);
foreach($arr as $val){
	$nTime = ($val / $this->max) * 100;
	$data .= "data[$i] = ".$nTime.";";
	$i++;
}
return $data;
}

     

    It takes an array, finds the max/min and number halfway between the min/max.

    The next part I am not sure what to do.

     

    I am making a line graph using the canvas, so I am building a javascript array, which will contain the point on the chart. The problem I am having, is location.

    Lets say $max = 110, and $min = 95.

    the y position on the graph should be 0 for $max, and 100 for $min, then all the other points need to fall into their place as well.

     

    $nTime is supposed to do that, but it doesn't any suggestions?

     

    The attachment is the result I am getting from my above code.

    As you can see the lowest number is 49, so the value with 49 should touch the bottom, which it doesn't.

    post-42401-13482403203042_thumb.png

    Cygwin Bash \r

    in Miscellaneous

    Posted

    Try this:

     

    height=${height%%$'\x0D'}
((height+=80))

     

     

    Sweet, that works!

     

     

    one more thing, how can I place a variable in a command?

     

    sub section of the command:

    $widthx$hight

     

    variables: $width, $height

     

    should display like so:

    1024x848

     

    I cant do that, because it seems to think that $widthx is the variable when it is just $width

    Problem with Select Union

    in MySQL Help

    Posted

    That has absolutely nothing do with with the "missing" rows -- they're missing because they're not distinct.  MySQL thinks it's doing you a favour.  That will indirectly make them non-distinct -- but that's not robust.

     

    Yes it does, they are usually no longer distinct if you add that column

     

    Not Distinct:

    1, table1

    1, table2

     

    removing what table they came from make them distinct

    Problem with Select Union

    in MySQL Help

    Posted

    What I commonly do when doing a union is add another column, and fill it with what table the row came from, not only does it give all of the rows, but now you have information on the table.

     

    select column_from_table_1 as col1, 'table1' as table from table_1 where something = 'some value'
union
select column_from_table_2 as col1, 'table2' as table from table_2 where something = 'some value'

     

    Note: I am not sure if this is a better solution than UNION ALL (probably not), but it is helpful IMO.

    Cygwin Bash \r

    in Miscellaneous

    Posted

    I am running a bash script in Cygwin and running this:

     

    height=`identify -format "%h" $image`

     

    it returns the height of the image such as:

    768

     

    but then when I try to add 80 to that number I can't because there is a \r at the end of it.

     

    how can I add the two numbers?

     

    Full Code:

    cd "C:\phpDocs\images"

image='Tulips.jpg'

width=`identify -format "%w" $image`
height=`identify -format "%h" $image`

((height+=80))

convert -size $widthx$height canvas:black new.jpg
composite -compose atop -gravity south -tile logo.png new.jpg new.jpg
composite -compose atop -gravity north Tulips.jpg new.jpg new.jpg

     

    Error:

    ")syntax error: invalid arithmetic operator (error token is "

    get_meta_tags(): Can Not Connect to Server

    in PHP Coding Help

    Posted

    Not following you here? Surely if you know the URL to get the tags, you can construct a cURL request instead?

     

    Currently all you can do is pass it a filename or a URL, it would be nice to be able to do this:

     

    // perform a curl request here, and get the html string back as $html_string
// $html_string = "<html>
//	<head>
//		<title>Title</title>
//		<meta name="myMeta1" content="my content" />
//		<meta name="myMeta2" content="my content 2" />
//		<meta name="myMeta3" content="my content 3" />
//	</head>
//	<!-- rest of html -->
//</html>";


$tags = get_meta_tags($html_string);
var_dump($tags);

    get_meta_tags(): Can Not Connect to Server

    in PHP Coding Help

    Posted

    After deciding to do a var_dump, which I should have done before posting, it seems as if on a failed connection it returns false, and everything else it returns an array for.

     

    That should have been in the documentation.

     

    Another thing that would have been nice is if this supported an html string so I could use cURL on on it, but oh well.

    Finding every Nth record in a database with a non-incremental index.

    in MySQL Help

    Posted

    This worked on a similar database for me. All you need to do is change customer_table_name_here to your table name.

     

    set @row = 0;
SELECT * 
FROM ( 
    	SELECT @row := @row +1 AS rownum, customer_index, customer_name
    	FROM ( 
		SELECT @row :=0
) r, customer_table_name_here order by customer_name
) ranked WHERE rownum % 120 in(1,0);

     

    <?php
$sql = mysql_query("query from above");
$c = 0;
while($row = mysql_fetch_assoc){
if($c == 0)
	echo "<a href="">".$row['customer_name']." - ";
if($c == 1){
	echo $row['customer_name']."</a><br />";
	$c = -1;
}
$c++;
}
if($c == 0);
echo "</a>";
?>

    Global Internet Treaty Worse Than SOPA

    in Miscellaneous

    Posted

    Someone needs to come up with a way so we can bypass ISP's and each individual computer can basically become its own ISP. That would be baller, and internet would be free!

     

    All it takes is running a few million miles of optical cable to tens of thousands of data centers around the world from your personal computer and you can have free internet.

     

    Or... Millions of people buy a fancy wireless antenna and connect to other wireless antennas creating a wireless network.

    backspace keypress not supported in chrome...

    in Javascript Help

    Posted

    I am using jquery, and I would like to force the first text in the text box to be http:// I would like it so a user can not delete that text.

     

    any suggestions on how to keep it there?

     

    I would use keypress(), but the backspace isn't supported in Chrome.

     

    keydown deletes the last "/"

    keyup deletes the last "/" then adds it back.

     

    Here is the code (replace keypress with keyup/keydown): 

    $("#domainTest").keypress(function(){
if(!$(this).val().match(/^http:\/\//)){
	$(this).val("http://");
	return false;
}
});

     

    So, what can I do?

    hMailServer Delivery Queue

    in Miscellaneous

    Posted

    I have installed hMailServer to send outgoing mail, but the one problem I am having is that mail is getting stuck in the Delivery Queue.

     

    Anyone have Any ideas why they are getting stuck?

     

    Here are the results of the Diagnostics test I ran from hMailServer:

    Test: Collect server details

    hMailServer version: hMailServer 5.3.3-B1879

    Database type: MSSQL Compact

     

    Test: Test IPv6

    IPv6 support is available in operating system.

     

    Test: Test outbound port

    SMTP relayer is in use.

    Trying to connect to host mail.weblyize.com...

    Trying to connect to TCP/IP address 24.179.144.72 on port 25.

    Connected successfully.

     

    Test: Test MX records

    Trying to resolve MX records for weblyize.com...

    Host name found: mail.weblyize.com

     

    Test: Test local connect

    Connecting to TCP/IP address in MX records for local domain domain weblyize.com...

    Trying to connect to host mail.weblyize.com...

    Trying to connect to TCP/IP address 24.179.144.72 on port 25.

    Connected successfully.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.