PHP - Slow Query Running/page Rendering While Mysql Event Is Being Executed

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=342696.0

Hi, I know this has to be possible but I have been unable to get this to work properly.

I have a page with two forms.   The first form is a list of sources associated with a subject, with checkboxes, so that it is possible to remove these associations, like so:

Code: [Select]
$qs = "SELECT s.source_id, s.source_name
from source s, source_subject ss, subject sb
where s.source_id = ss.source_id
and sb.subject_id = ss.subject_id
and sb.subject_id = $subject_id
order by source_name";
$rs  = mysqli_query($dbc, $qs)
  or die ('Error querying database');


while($row = mysqli_fetch_array($rs)) {


echo '<input type="checkbox" value="' . $row['source_id'] . '" name="markdelete[]">' . $row['source_name'] . '<br />' ;

}


The second form is a list of sources that are not associated with this subject, with checkboxes enabling the addition of more sources to this subject, like so:

Code: [Select]
$r = "SELECT source_id, source_name
FROM source
WHERE source_id NOT IN
(select s.source_id
from source s, source_subject ss
where s.source_id = ss.source_id
and ss.subject_id = '$subject_id')";

$r1  = mysqli_query($dbc, $r)
or die ('Update Error: '.mysqli_error($dbc));


while ($row = mysqli_fetch_array($r1)) {
echo '<input type="checkbox" id="source_id" name="source_id[]" ' ;

echo 'value="'. $row['source_id'] .'"';


    echo '> ' . $row['source_name'] . '<br />';


} 


The first form works fine... when the "remove" submit button is clicked, it successfully removes any selected sources and then shows the remaining sources still associated, and then automatically populates this source in the below list (form 2) as one that is not selected.

On the second form, when adding a new source to the subject, it successfully enters the data into the database, and removes this source from the list of unselected sources, but it does not seem to re-populate in the first form.

The data is correct on the tables, but the page needs to reload the first query... how do I get it to do this?  I'd like to be able to make this page editable, and re-editable (in case someone makes a mistake) without having to go back and reload the entire page.

Note, both forms call the page itself; not sure if this is part of it.  Using this:
Code: [Select]
<form  method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?subject_id='.$subject_id ;    ?>"> 
Does anyone have any ideas?

Hey Guys,

I have a DB with two tables, one is field names, the other is the data.

So for example, I log in and I am user ID 1..... I need to query the DB to show all fields where user_id = 1

Field1 - Data1
Field2 - Data2

etc etc...

This is what I have so far, but I dont get any output...

    $sql = "SELECT * FROM ".WPSC_TABLE_DATA." WHERE `user_id` == .$cu. ORDER BY id DESC";
    $user= $wpdb->get_row($sql);

while($user) {

echo $user->name .' : '. $user->value .'<br />';

}

Can you help please?

Its the

Code: [Select]
$query = "INSERT INTO hitlist
 hit_id = ($addIDs_str), player_id = '$playerID'";
Thats the issue, my codes below i cannot figure out how to insert the results, any help would be appreciated Thanks

Code: [Select]
if (isset($_POST["submit"]) && $_POST["submit"] == "Recall Selected Informants")
{
//Force POST values to be INTs
$addIDs_ary = array_map('intval', $_POST['chkInv']);
//Remove any 'false' value
$addIDs_ary = array_filter($addIDs_ary);
//Check that there was at least one valid value
if(count($addIDs_ary))
{
//Create comma separated string of the IDs
$addIDs_str = implode(',', $addIDs_ary);
//Create and run one query to perform all the adds (of the user)
$query = "INSERT INTO hitlist
 hit_id = ($addIDs_str), player_id = '$playerID'";
$sql = "UPDATE players SET Informants = Informants - 1 WHERE id = '$playerID'";
mysql_query($sql) or die(mysql_error());
if(mysql_query($query))
{
$selectedCount = count($addIDs_ary);
$adddCount  = mysql_affected_rows();
echo "{$adddCount} of {$selectedCount} Investigated player(s) were successfully Removed.";
if($adddCount != $selectedCount)
{
echo " You do not have rights to the others.";
}
}else{
echo "There was a problem running the query.<br>" . mysql_error();
}
}else{
echo "Invalid ID data passed.";
}
}

Hi,

I have written some code however when I echo the query it runs it twice...

Quote

SELECT `description`, `fulldescription` FROM `productdbase` WHERE `keywords` LIKE '%table%'something found.SELECT `description`, `fulldescription` FROM `productdbase` WHERE `keywords` LIKE '%table%'something found.


I only have the query once in my code. Any suggesions?

Code: [Select]
<?php
if (isset($_POST['keywords'])){
 $keywords = mysql_real_escape_string (htmlentities(trim($_POST['keywords'])));
}

$errors = array();

if (empty($keywords)) {
$errors[] = 'Please enter a search term';
} else if (strlen($keywords)<3) {
$errors[] = 'Your search must be three or more characters';
} else if (search_results($keywords) === false) {
$errors[] = 'Your search for '.$keywords.' returned no results';
}

if (empty($errors)) {

search_results ($keywords);

} else{
foreach($errors as $error) {
echo $error, '</br>';
}
}


    ?>


<?php
function search_results ($keywords) {
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword) {
$where .= "`keywords` LIKE '%$keyword%'";
if ($key != ($total_keywords - 1)) {
$where .= " AND ";

}
}

$query_string = "SELECT `description`, `fulldescription`  FROM `productdbase` WHERE $where";
echo $query_string;
$query = mysql_query($query_string);



if ($results_num === 0) {
return false;
}else{
echo 'something found.';
}




}
?>

Hi guys I need some help with slow connection to remote mysql database. I've been after this for a couple of days. I've been reading around but I didn't find any solution which helped me. I have this scenario:   Server A
e-commerce running
mysql database running   Server B
e-commerce running
remote connection to server A database   The two e-commerce are identical. On server A it runs very quick, on server B the connection to mysql database it's about 16 times slower. Some complex queries and mixes queries don't get resolved and let the page go in timeout. I tried a test php script where I connect to mysql and I run a simple query (some classes are required before the query). On server A execution time is 0.107 seconds while on server B it is 1.788 seconds. I tried to have the same test with both firewall (server A and B) turned off but the results is exactly the same. I added in my.cnf of server A skip-name-resolve and restarted mysql but this didn't really change anything I am logging slow queries and none from server B are recorded in the file Running the same query from remote ssh connection from server B to server A's mysql is very fast Connection is set to persistent on server B's e-commerce Both server have centos 6.2 installed with WHM/Cpanel, configurations are basically the same. Do I miss something obvious?

I have a php script that I've been running that seems to have been working but now I'm wondering if some of my logic is potentially off.

I select records from a db table within a date range which I put into an array called ```$validCount```

If that array is not empty, that means I have valid records to update with my values, and if it's empty I just insert. The trick with the insert is that if the ```STORES```  is less than the ```Quantity``` then it only inserts as many as the ```STORES``` otherwise it inserts as many as ```Quantity```.

So if a record being inserted with had 

    Stores: 14 Quantity:12

Then it would only insert 12 records but if it had
  
    Stores:1  Quantity:20

It would only insert 1 record.

In short, for each customer I should only ever have as many valid records (within a valid date range) as they have stores. If they have 20 stores, I can have 1 or 2 records but should never have 30.

It seems like updating works fine but I'm not sure if it's updating the proper records, though it seems like in some instances it's just inserting too many and not accounting for past updated records.

This is the logic I have been working with:

 

  if(!empty($validCount)){
            for($i=0; $i<$row2['QUANTITY']; $i++){
                try{
                    $updateRslt = $update->execute($updateParams);
                }catch(PDOException $ex){
                    $out[] = $failedUpdate;
                }
            }
    }else{    
        if($row2["QUANTITY"] >= $row2["STORES"]){
            for($i=0; $i<$row2["STORES"]; $i++){     
                try{
                    $insertRslt = $insert->execute($insertParams);
                }catch(PDOException $ex){
                    $out[] = $failedInsertStore;
                }
            }
        }elseif($row2["QUANTITY"] < $row2["STORES"]){
            for($i=0; $i<$row2["QUANTITY"]; $i++){     
                try{
                    $insertRslt = $insert->execute($insertParams);
                }catch(PDOException $ex){
                    $out[] = $failedInsertQuantity;
                }
            }
        }
    }

 

Let's say customer 123 bought 4 of product A and they have 10 locations

    customerNumber  |  product  |  category  |  startDate  |  expireDate  | stores
    ----------------------------------------------------------------------------------
    123                 1           A           2018-08-01    2019-03-01      10
    123                 1           A           2018-08-01    2019-03-01      10
    123                 1           A           2018-08-01    2019-03-01      10
    123                 1           A           2018-08-01    2019-03-01      10

Because they purchased less than their store count, I insert 4 records. Now if my ```$validCheck``` query selects all 4 of those records (since they fall in a valid date range) and my loop sees that the array isn't empty, it knows it needs to update those or insert. Let's say they bought 15 this time. Then I would need to insert 6 records, and then update the expiration date of the other 9 records. 

    customerNumber  |  product  |  category  |  startDate  |  expireDate  | stores
    ----------------------------------------------------------------------------------
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10
    123                 1           A           2018-08-01    2019-03-11      10

There can only ever be a maximum of 10 (store count) records for that customer and product within the valid date range. As soon as the row count for that customer/product reaches the equivalent of stores, it needs to now go through and update equal to the quantity

so now I'm running this but it's not running and no errors, but it just returns back to the command line   

$total = $row2['QUANTITY'] + $validCheck;
    if ($total < $row2['STORES']) {
        $insert_count = $row2['QUANTITY'];
        $update_count = 0;
    } else {
        $insert_count = $row2['STORES'] - $validCheck; // insert enough to fill all stores
        $update_count = ($total - $insert_count); // update remainder
    }

        
    for($i=0; $i<$row2['QUANTITY']; $i++){
        try{
            $updateRslt = $update->execute($updateParams);
        }catch(PDOException $ex){
            $failedUpdate = "UPDATE_FAILED";
            print_r($failedUpdate);
            $out[] = $failedUpdate;
        }
    }
    
        
    for($i=0; $i<$insert_count; $i++){
        try{
            $insertRslt = $insert->execute($insertParams);
        }catch(PDOException $ex){
            $failedInsertStore = "INSERT_STORE_FAILED!!!: " . $ex->getMessage();
            print_r($failedInsertStore);
            $out[] = $failedInsertStore;
        }
    }```

    

My my.cnf file has three sections (mysqld, mysqld_safe, mysql).  It seems that they represent different modes which MySQL runs in.  Is this correct?  I read that some recommend running in safe mode.  Should I be if it is being used to support PHP?  I typically start MySQL as "service mysqld start" when running Centos. How should I be starting it if I want it in safe mode.
 

Hey,

I am a bit of a noob at php/mysql and trying to be clever(emphasis on trying).

I want to pull some php/html code from a database in mysql, whilst it does display the html, the php does not.
Can this even be done? Is it going to cause me issues later on?

Code: [Select]
[php]
<?php
    $query = "SELECT * FROM pages";
    $result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)){
$body = $row["body"];
echo $body;
    }?>
[/php]
Any help would really be appreciated.

Ell

I am trying to create a calendar of Events for my company's website. When I search for this type of calendar, I can find code that runs the calendar from one Events table. However, we could have an Event that has multiple performances. (For example, Lily Tomlin could be performing at the Atwood Concert Hall in Anchorage on 09/09/11 and at the Vagabond Blues in Palmer on 09/10/11.) So we have two tables to draw the information from: Events and Performance

I can successfully create a MySQL query (see below) with PHP coding that will make a listing (as demostrated on our website http://www.centertix.net/).
Code: [Select]
"SELECT Events.PublishDate, Events.EventOnSaleDate, Events.BldgContractDate, Events.SetUpComplete, Events.EventTitle, Performance.PerfID, Performance.startDateTime,
Performance.endDateTime, Performance.PerfType, venues.VenueName, venues.VenueCode FROM (Performance RIGHT JOIN Events ON Performance.EventID = Events.EventID)
LEFT JOIN venues ON Performance.VenueCode = venues.VenueCode WHERE Events.BldgContractDate Is Not Null AND Events.SetUpComplete Is Not Null AND Performance.PerfType='Public Event'"However, I'm not advanced enough to create the if-clause or while-loop to check to see if the Performance.startDate = the calendar's date and then place the info into the appropriate calendar grid box. I think have determined where it should be place in my PHP code, but need help getting the calendar to work.

Here's the code where I think it should go:
Code: [Select]
         for($list_day = 1; $list_day <= $days_in_month; $list_day++):
if(($list_day == date("j",mktime(0,0,0,$this->month))) &&
(date("F") == date("F",mktime(0,0,0,$this->month))) &&
(date("Y") == date("Y",mktime(0,0,0,$this->month,$list_day,$this->year)))) {
                 $this->calendar.= '<td class="'. $this->style .'-current-day">';
             } else {   
                 if(($running_day == "0") || ($running_day == "6")) {
                     $this->calendar.= '<td class="'. $this->style .'-weekend-day">';
                 } else {
                     $this->calendar.= '<td class="'. $this->style .'-day">';   
                 }
             }
           
                 /* add in the day number */
                 $this->calendar.= '<div class="'. $this->style .'-day-number">'.$list_day.'</div>';
   
                 /** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
                 $this->calendar.= '<div class="'. $this->style .'-text"><a href="">PERFORMANCE INFO</a></div>'. "\n";
                 /** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF NO MATCHES FOUND, PRINT NOTHING !! **/
                 $this->calendar.= str_repeat('<p> </p>',2);
               
             $this->calendar.= '</td>';
             if($running_day == 6):
                 $this->calendar.= '</tr>';
                 if(($day_counter+1) != $days_in_month):
                     $this->calendar.= '<tr class="'. $this->style .'-row">';
                 endif;
                 $running_day = -1;
                 $days_in_this_week = 0;
             endif;
             $days_in_this_week++; $running_day++; $day_counter++;
         endfor;

Please help me plug the calendar in with the appropriate events! If you would like to see how far I have gotten, you can check out this link: http://www.myalaskacenter.com/calendars/calendarSample2.php

would Java handle it faster? can PHP be made to handle it as fast? I really need help.

check out my website and search cars for sale, then any distance and then 37919 zip. it takes too long and there are only 3351 cars on there now. before, when it only had about 2200 cars it was really fast. only about a 1000 cars made it slow. what could it be? nabacar.com It was returning results in about 15 seconds or less with 2200 cars. Now it is over a minute with only 1000 cars on it. I will have 150,000 on it when I get this sorted.

http://nabacar.com

some java was written to speed things up, but it didn't seem to help. Is it the server or the code causing this?

Hello again,

I got a problem with one of my pages that uses MySql. I didn't expect my site to shoot up into the 1000's per hour but it has but has destroyed the loading time of one of my pages.

Below is the function that is super slow!
Code: [Select]
function get_top($timeframe='today', $time=10, $friends=true, $limit=5, $unique=true, $me_only=false){
if($friends===false){$q='';}else{
$q=' AND (username IN ('; // This array can have upto 500 items in it.
global $friend_id_array, $me;$tarray=$friend_id_array; $tarray[]=$me['id'];
$x= implode(',',$tarray);$q.=$x;
$q.= '))';
}
if($me_only){
$q=' AND username = \''.$me['id'].'\' ';
}

if($timeframe=='today'){
$q_timeframe='AND DATE(thedate) = DATE(NOW()) AND YEAR(thedate) = YEAR(NOW())'; // Checking the date could be slow
}else
if($timeframe=='week'){
$q_timeframe='AND WEEK(thedate) = WEEK(NOW()) AND YEAR(thedate) = YEAR(NOW())';
}else
if($timeframe=='month'){
$q_timeframe='AND MONTH(thedate) = MONTH(NOW()) AND YEAR(thedate) = YEAR(NOW())';
}else
if($timeframe=='all'){
$q_timeframe='';
}
if($unique){$q_unique='GROUP BY username';}
$query="SELECT MAX(score) as highscore, username, timeframe, thedate FROM other_click WHERE (timeframe = '$time') $q $q_timeframe $q_unique ORDER BY highscore  DESC LIMIT 0, $limit"; // A very long query
$result=@mysql_query($query)or die(mysql_error());
if(@mysql_num_rows($result)>0){
while($row=@mysql_fetch_array($result)){ // Maybe resorting the array makes it super slow
$id_array[]=$row;
}
return $id_array;}else{return false;}
}
I've commented the lines which I think maybe making this page load slowly.

I also thought the way the page is loaded.
The page is called 6 times per user but with different settings eg, Friends only and only scores added today.

Please help because this is crippling my site.

Thank-you all
Paul

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>