I am doing work on a php project. I have my own sign in and sign up.Now I want that when a user sign in or sign up the website gets custeomize for h...More

I am doing work on a php project. I have my own sign in and sign up.Now I want that when a user sign in or sign up the website gets custeomize for him and his name appears on top as in websites.How can I do this do I need to create user session in header.And also don't know about its coding so anyone can help me plz.

enter code here

?> ">

Please enter your login detail

 <?php if(isset($_REQUEST['errmsg'])){?>
  <tr>
      <td style='color:red'><?php echo base64_decode($_REQUEST['errmsg']);?></td>
  </tr>
  <?php } ?>
<tr>
<td>
<img src="images/images.jpg" width="150" height="150" alt="lock" />
</td>    <td>    <p>Username:</p>    <p><input type="text" name="username" id="username" ></p>    <p>Password:</p>    <p><input

type="password" name="password" id="password">

enter code here
<?php include_once 'Connections/db_autodealers.php';?>
<link rel="stylesheet" type="text/css" href="<?php echo $base_url?>style.css"/>
<!--<script src='<?php echo $base_url;?>js/jquery.min.js' type="text/javascript"></script>-->
<script src='<?php echo $base_url;?>js/jquery-1.8.2.min.js' type="text/javascript"></script>
<script type="text/javascript" src="<?php echo $base_url.'js/jquery.ajaxq-0.0.1.js'?>"></script>
<script type="text/javascript" src="<?php echo $base_url.'js/common.js'?>"></script>
<link rel="stylesheet" type="text/css" href='<?php echo $base_url;?>uploadify.css'/>
<header>
<hr id='start1'></hr>
<table width="1200" height="100">
<tr>
<td>
<div  class="menu_div1">
<ul>
<li id="active"><a href="<?php echo $base_url.'signup.php'?>" id="current">sign up</a>|</li>
<li><a href="<?php echo $base_url.'signin.php'?>">sign in</a></li>
</ul>
</div>
</td>
</tr>
  <tr>
 <td width="431" height="80"  > <img src="images/logo1.jpg"  style="margin-left:200px"  width="202" height="80" alt="autodealers"

/>

<td width="757">
<div id='cssmenu'>      
<ul>
   <li><a href="<?php echo $base_url ?>"><span>Home</span></a></li>
   <li class='has-sub'><a href="<?php echo $base_url.'newcars.php'?>"><span>NEW CARS</span></a>
      <ul>
     <li><a href="<?php echo $base_url.'comparecars.php'?>"><span>CAR COMPARISON</span></a></li>
               <li><a href="<?php echo $base_url.'carreviews.php'?>"><span>REVIEWS</span></a></li>
                <li><a href='#'><span>Price List</span></a></li>

         </li>
      </ul>
   </li>
   <li><a href="<?php echo $base_url.'usedcars.php'?>"><span>USED CARS</span></a></li>
   <li><a href="<?php echo $base_url.'ride.php'?>"><span>RIDES</span></a></li>
   <li><a href='#'><span>FORUMS</span></a></li>
   <li><a href='#'><span>ACCESSORIES</span></a></li>
   <li><a href='#'><span>BLOG</span></a></li>
</ul>
</div>
</td>
   </tr>
      </table>
  </header>

I am very new to MySQL, can you guys please help me in this?

Query:

UPDATE users u,
       posts p
SET    u.tags = (SELECT g...More

I know this has been asked before at least in this thread: More

I know this has been asked before at least in this thread: is php sort better than mysql "order by"?

However, I'm still not sure about the right option here since the performance on doing the sorting on PHP side is almost 40 times faster. This MySQL query runs in about 350-400ms

SELECT 
keywords as id, 
SUM(impressions) as impressions, 
SUM(clicks) as clicks, 
SUM(conversions) as conversions, 
SUM(not_ctr) as not_ctr, 
SUM(revenue) as revenue, 
SUM(cost) as cost 
FROM visits WHERE campaign_id = 104 GROUP BY keywords(it's an integer) DESC

Keywords and campaign_id columns are indexed.

Using about 150k rows and returns around 1500 rows in total. The results are then recalculated (we calculate click through rates, conversion rates, ROI etc, as well as the totals for the whole result set). The calculations are done in PHP.

Now my idea was to store the results with PHP APC for quick retrieval, however we need to be able to order these results by the columns as well as the calculated values, therefore if I wanted to order by click-through rate I'd have to use (SUM(clicks) / (SUM(impressions) - SUM(not_ctr)) within the query which makes it around 40ms slower and the initial 400ms is a really long time already.

In addition we paginate these results, but adding LIMIT 0,200 doesn't really affect the performance.

While testing the APC approach I executed the query, did the additional calculations and stored the array in memory so it would only be executed once during the initial request and that worked like a charm. Fetching and sorting the array from memory only took around 10ms, however the script memory usage was about 25mb. Maybe it's worth loading the results into a memory table and then querying that table directly?

This is all done on my local machine(i7, 8gb ram) which has the default MySQL install and the production server is a 512MB box on Rackspace on which I haven't tested yet, so if possible ignore the server setup.

Im trying to update records in my table with the followin, my problem is however that my browser outputs an empty white page with no source, Can any...More

Im trying to update records in my table with the followin, my problem is however that my browser outputs an empty white page with no source, Can anybody see what I'm doing wrong?

<?php

require 'dbconfig.php';

//Always place this code at the top of the Page
session_start();
if (!isset($_SESSION['id'])) {
    // Redirection to login page twitter or facebook
    header("location: index.php");
}



function safe($value){
   return mysql_real_escape_string($value);
}

// Variables
$_SESSION['username'];
$_SESSION['oauth_provider'];
$uid = $_SESSION['id'];
$email = safe($_POST["email"]);
$credits = safe($_POST["credits"]);



 $query = mysql_query("UPDATE users SET email= '$email' WHERE id='$uid'") or die(mysql_error());


?>

I update row in DB and check for affected row but its not working with rowCount() and also mysql_affected_rows.

This is my code

More

I update row in DB and check for affected row but its not working with rowCount() and also mysql_affected_rows.

This is my code

$result = $this->_db_table->update(array('password' => $new_password), $where);

I have checked in my DB and it changes pass but when i

$affectedRows = mysql_affected_rows($result);

or

$affectedRows = $result->rowCount();

it's always return FALSE with mysql_affected_rows and 500 error server with rowCount(). I test by curl , Any ideas???

I have been wrestling with highcharts and json_encode via php for a few days now. I believe I have the data formatted properly but yet the chart is...More

I have been wrestling with highcharts and json_encode via php for a few days now. I believe I have the data formatted properly but yet the chart is not updating properly. The category data is updated and the series data to chart the column and line data is formatted as it should be.

Can anyone help me get the rest of the way. Here is the php script and highcharts javascript I am using.

Thank you for your help:

<?php          
// data feed to power dynamic charts
include_once('db_conn.php');

// get site data and store in array
$query = mysql_query("SELECT site, SUM(impressions) AS impressions, SUM(clicks) AS     clicks FROM dfa_data WHERE campaign='$campaign' and time='$time' GROUP BY site ORDER BY   impressions DESC");
$rows = array();
while($r = mysql_fetch_array($query)) {
    $rows['categories'][] = $r['site'];
}

// get impression data and store in array
$query = mysql_query("SELECT site, SUM(impressions) AS impressions, SUM(clicks) AS clicks FROM dfa_data WHERE campaign='$campaign' and time='$time' GROUP BY site ORDER BY    impressions DESC");
$rows1 = array();
$rows1['name'] = 'Impressions';
$rows1['color'] = '#4572A7';
$rows1['type'] = 'column';
$rows1['yAxis'] = 1;
while($rr = mysql_fetch_array($query)) {
    $rows1['data'][] = $rr['impressions'];
}

// get ctr data and store in array
$query = mysql_query("SELECT site, SUM(impressions) AS impressions, SUM(clicks) AS clicks FROM dfa_data WHERE campaign='$campaign' and time='$time' GROUP BY site ORDER BY impressions DESC");
$rows2 = array();
$rows2['name'] = 'CTR';
$rows2['color'] = '#89A54E';
$rows2['type'] = 'spline';
while($rrr = mysql_fetch_array($query)) {
 $ctr = number_format(($rrr['clicks']/$rrr['impressions'])*(100),2,'.',',');
 $impressions = number_format($rrr['impressions'],0,'.',',');
 $clicks = number_format($rrr['clicks'],0,'.',',');
    $rows2['data'][] = $ctr;
}

$result = array();
$result1 = array();
array_push($result,$rows);
array_push($result1,$rows1);
array_push($result1,$rows2);


?>

<script>
$(function () {
     $('#container').highcharts({
        chart: {
            zoomType: 'xy'
        },
        title: {
            text: 'Performance by Site'
        },
        subtitle: {
            text: ''
        },
        xAxis: 
        <?php echo json_encode($result); ?>
        ,
        yAxis: [{ // Primary yAxis
            labels: {
                format: '{value}%',
                style: {
                    color: '#89A54E'
                }
            },
            title: {
                text: 'CTR',
                style: {
                    color: '#89A54E'
                }
            }
        }, { // Secondary yAxis
            title: {
                text: 'Impressions',
                style: {
                    color: '#4572A7'
                }
            },
            labels: {
                format: '{value}',
                style: {
                    color: '#4572A7'
                }
            },
            opposite: true
        }],
        tooltip: {
            shared: true
        },
        series: <?php echo json_encode($result1); ?>
     });
  });
</script>

var_dump($result) outputs the following

array(1) { [0]=> array(1) { ["categories"]=> array(5) { [0]=> string(13) "Search Medica" [1]=> string(10) "Medscape 4" [2]=> string(11) "onclive.com" [3]=> string(22) "Oncology Nurse Advisor" [4]=> string(25) "chemotherapyadvisor.com 1" } } }

var_dump($result1) outputs the following

string(7) "result1"

Here is the json_encode output as well for both:

json_encode $result

[{"categories":["Search Medica","Medscape 4","onclive.com","Oncology Nurse Advisor","chemotherapyadvisor.com 1"]}]

json_encode $result1

[{"name":"Impressions","color":"#4572A7","type":"column","yAxis":1,"data":[140521,71905,69295,68456,49487]},{"name":"CTR","color":"#89A54E","type":"spline","data":[0.11,0.04,0.2,0.09,0.05]}]

I have two Tables one for Teams one for Players What I am trying to find out is the total head count table, In other words I want to have a cou...More

I have two Tables one for Teams one for Players What I am trying to find out is the total head count table, In other words I want to have a count of the total number of teams that have 2 members, the to all number of teams that have 3 members etc

Here is the database structure.

(Sidebar Question: I'm a newbee here: Is there a better way to post the SQL? )

CREATE  TABLE `formsfiles`.`Teams` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) );


INSERT INTO `Teams` (`Name`) VALUES ('Sharks');
INSERT INTO `Teams` (`Name`) VALUES ('Jets');
INSERT INTO `Teams` (`Name`) VALUES ('Fish');
INSERT INTO `Teams` (`Name`) VALUES ('Dodgers');


CREATE  TABLE `Players` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  `Team_ID` INT NULL ,
  PRIMARY KEY (`ID`) );

INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Jim', '1');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Tom', '1');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Harry', '2');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Dave', '2');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Tim', '3');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Trey', '4');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Jay', '4');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Steve', '4');
INSERT INTO `Players` (`Name`, `Team_ID`) VALUES ('Chris', '4');

What I want is a count Team sizes.

I would like to see the following output

Team_Size  Count
1          1
2          2
4          1

What is the difference between pip's mysql-python and apt-get's python-mysqldb?

$ pip search mysql-python
MySQL-python    - Python i...More

I have a script that Im pulling data from a form when I do var_dump($_POST) I get

array(2) { ["objectName"]=> string(7) "TestOBJ...More

Info: I have this table (PERSONS):

PERSON_ID   int(10)
POINTS      int(6)
4 OTHER COLUMNS which are of typ...More

I'm not sure but I changed up this code to try to get it to put the value 7777777777777 into the database it always puts this old serial_number 2147...More

I'm not sure but I changed up this code to try to get it to put the value 7777777777777 into the database it always puts this old serial_number 2147483647 value stored in database (and even when I removed that record with 2147483647 serial nunmber) it still inserts the record with 2147483647 instead of 7777777777777 in database:

def create
    @cart = current_cart
    quantity = nil
    resource = nil
    args = {}

    args[:quantity] = is_number?(params[:quantity]) ? params[:quantity].to_i : 1
    if params[:sellable_type] == "Product"
      @product = Product.find(params[:sellable_id])
      args[:resource] = @product
    elsif params[:sellable_type] == "AirtimePlan"
      @airtime_plan = AirtimePlan.find(params[:sellable_id])
      if @cart.airtime_list_items.any?
        flash[:notice] = {:too_many_plans => "Can only purchase one airtime plan at a time"}
        render "airtime_plans/show" and return
      end

      if ['MarineTrac','MotoTrac','FleetTrac','AutoTrac'].include? params[:unit_type]
        args[:unit_type] = params[:unit_type]      
      else
        flash[:notice] = {:nonexistant_plan => "No such plan exists"}
        render "airtime_plans/show" and return
      end

      args[:resource] = @airtime_plan
    end

    args[:serial_number] = params[:serial_number]

    @line_item = @cart.add_sellable_item args

    #save line item to update its quantity, this will also save association with carton
    if @line_item.save  
      redirect_to cart_path(@cart), notice: 'Line item created.'
    else
      if params[:sellable_type] == "Product"
        render "products/show"
      else
        flash[:notice] = @line_item.errors.messages
        render "airtime_plans/show"
      end
    end
  end

cart model

def add_sellable_item(args={})#resource, _quantity, serial_number=nil
    resource = args[:resource]
    _quantity = args[:quantity]
    serial_number = args[:serial_number] if args[:serial_number]
    unit_type = args[:unit_type] if args[:unit_type]

    current_item = self.line_items.where(sellable_type: resource.class.name, sellable_id: resource.id).first

    if current_item && current_item.sellable.is_a?(Product)
      current_item.quantity += _quantity 
    else
      new_item = self.line_items.reload.create!(sellable_id: resource.id, sellable_type: resource.class.name, serial_number: 7777777777777)
      puts "What is the serial number #{new_item.serial_number}"

      new_item.quantity = _quantity
      new_item.unit_type = unit_type if unit_type
      return new_item
    end

    current_item
  end

I tried using reload, I tried forcing a create! and I tried explicitly setting the serial_number, and it still creates the record with a serial_number of 2147483647. I even have validates_uniquness_of constraints on the line item model.

Check this log out, it makes sense:

Started POST "/line_items" for 127.0.0.1 at 2013-05-21 14:01:18 -0400
Processing by LineItemsController#create as HTML
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"IOXew7ITmoysfrxHO3CYwYB2b7TLFhVR7JgwhwxqwXg=", "serial_number"=>"102099394", "unit_type"=>"MarineTrac", "sellable_id"=>"118", "sellable_type"=>"AirtimePlan", "commit"=>"Add to Cart"}
   (0.1ms)  BEGIN
  SQL (0.4ms)  INSERT INTO `line_items` (`cart_id`, `created_at`, `order_id`, `quantity`, `sellable_id`, `sellable_type`, `serial_number`, `unit_type`, `updated_at`) VALUES (NULL, '2013-05-21 18:01:18', NULL, 1, NULL, NULL, 7777777777777, NULL, '2013-05-21 18:01:18')
Mysql2::Error: Duplicate entry '2147483647' for key 'index_line_items_on_serial_number': INSERT INTO `line_items` (`cart_id`, `created_at`, `order_id`, `quantity`, `sellable_id`, `sellable_type`, `serial_number`, `unit_type`, `updated_at`) VALUES (NULL, '2013-05-21 18:01:18', NULL, 1, NULL, NULL, 7777777777777, NULL, '2013-05-21 18:01:18')
   (0.5ms)  ROLLBACK
Completed 500 Internal Server Error in 14ms

ActiveRecord::RecordNotUnique (Mysql2::Error: Duplicate entry '2147483647' for key 'index_line_items_on_serial_number': INSERT INTO `line_items` (`cart_id`, `created_at`, `order_id`, `quantity`, `sellable_id`, `sellable_type`, `serial_number`, `unit_type`, `updated_at`) VALUES (NULL, '2013-05-21 18:01:18', NULL, 1, NULL, NULL, 7777777777777, NULL, '2013-05-21 18:01:18')):
  app/controllers/line_items_controller.rb:8:in `create'

i want to execute a user submitted sql code in my server (for teaching purposes). But this can be very dangerous. I want to know what will be better...More

i want to execute a user submitted sql code in my server (for teaching purposes). But this can be very dangerous. I want to know what will be better, if execute the sql code (if there is a secure way to do this) or only check if the user code matches a regular expression and show a simbolic result.

Thanks in advanced !

i am working on a project, in which i need to send two alerts through email. i have implemented one email alert and its working fine and i'm getting...More

i am working on a project, in which i need to send two alerts through email. i have implemented one email alert and its working fine and i'm getting status message as completed after successfull delivery of email but for the second one i have added one more column as service_status. although the email functionality is working fine but its doesnot showing completed instead its showing null or empty value, but the value in database is "completed"... Here is my peice of code where i need to show the completed result

  <?php 
if(count($det)){
?>

<?php for($i=0;$i<count($det);$i++)
{?>
 <tr class="rowvalues">
<td rowspan="2" class="bottomcolor">
        <input  type="checkbox"  name="list[]"  value="<?php echo $det[$i]['customer_id']; ?>" /></td>
<td rowspan="2" class="bottomcolor"><?php echo $det[$i]['customer_title'].".".$det[$i]['customer_surname']; ?></td>
<td rowspan="2" class="bottomcolor"><?php echo $det[$i]['vehicle_regno']; ?></td>
<td>Reminder1</td>
<td><?php
        if ($det[$i]['reminder1_duedate'] != '0000-00-00'){
         echo date('d/m/Y',strtotime($det[$i]['reminder1_duedate']));}else{echo '00/00/0000';} ?></td>
<td><?php if ($det[$i]['reminder1_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder1_duedate'].'- 28 days'));}else{echo '00/00/0000';} ?></td>
<td><?php if ($det[$i]['reminder1_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder1_duedate'].'- 14 days'));}else{echo '00/00/0000';} ?></td>
<td><?php if ($det[$i]['reminder1_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder1_duedate'].'- 2 days'));}else{echo '00/00/0000';} ?></td>
<td><?php echo isset($det[$i]['reminder1_status'])?($det[$i]['status']=='c')?'Completed':'Pending':'';?>           </td>
<td rowspan="2" class="bottomcolor"><a href="<?php echo base_url();?>preview/view_vehicle.php?vid=<?php echo $det[$i]['customer_id'];?>" rel="facebox"><img src="<?php echo base_url();?>images/view.gif" alt="View"/></a>
       <a style="text-decoration:none;" href="<?php echo base_url();?>index.php/vehicles/edit_mot/<?php echo $det[$i]['customer_id'];?>" title="Edit"> <img src="<?php echo base_url();?>images/edit.gif" alt="search"/></a> <a style="text-decoration:none;" href="<?php echo base_url();?>index.php/vehicles/view/delete/<?php echo $det[$i]['customer_id'];?>" onclick="return confirm('Are you sure you want to delete this Vehicle Details?');" title="Delete">
        <img src="<?php echo base_url();?>images/delete.gif" alt="delete"/></a></td>
    </tr>
    <tr>
    <td class="bottomcolor">Reminder 2</td>
     <td class="bottomcolor"><?php
        if ($det[$i]['reminder2_duedate'] != '0000-00-00'){
         echo date('d/m/Y',strtotime($det[$i]['reminder2_duedate'])); }else{echo '00/00/0000';}?></td>
    <td class="bottomcolor"><?php if ($det[$i]['reminder2_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder2_duedate'].'- 28 days'));}else{echo '00/00/0000';}?></td>
    <td class="bottomcolor"><?php if ($det[$i]['reminder2_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder2_duedate'].'- 14 days'));}else{echo '00/00/0000';}?> </td>
    <td class="bottomcolor"><?php if ($det[$i]['reminder2_duedate'] != '0000-00-00'){ echo  date('d/m/Y',strtotime($det[$i]['reminder2_duedate'].'- 2 days'));}else{echo '00/00/0000';}?> </td>
    <td class="bottomcolor"><?php echo isset($det[$i]['reminder2_status'])?($det[$i]['service_status']=='c')?'Completed':'Pending':'';?>          </td>
    </tr>

             <?php } 
          }
     else{ echo '<div class="row"><p  style="text-align:center;"><strong>No records found</strong></p></div>'; 
    } ?>

  </table>      

The problem is from this line of code

        <td class="bottomcolor"><?php echo isset($det[$i]['reminder2_status'])?($det[$i]['service_status']=='c')?'Completed':'Pending':'';?>          </td>

This is returning empty or null value

I was trying a set up for my tables, which seemed simpler to me, but I am not managing to find the proper way.

This is to calculate after s...More

I was trying a set up for my tables, which seemed simpler to me, but I am not managing to find the proper way.

This is to calculate after sales costs for vehicles where each vehicle will receive one of each of a sub-group of services from a price list to make up the total after sales cost.

Please see the scenario below:

Table AfterSalesPriceList would be like:

as_id   as_description      as_value

1       service kit1          1000
2       service kit2          1500
3       service kit3          2000
.......
10      repair kit1           5000
11      repair kit2           8000
12      repair kit3           9000
.....
21      pneu 1                1500
22      pneu 2                2500
23      pneu 3                4000
.....
31      battery 1              500
32      battery 2              800
33      battery 3              1000

Now, in another table? I would place which of these services each vehicle will receive.Each vehicle has to have one and ONLY 1 of each item from service, repair, pneu, battery,etc, like in a Table VehicleAS:

vehicle_id 1    service kit1, repair kit 2, pneu 3, battery 1
vehicle_id 2    service kit2, repair kit 2, pneu 1, battery 2
etc.....

Of course, this does not work in mysql, at least far as I know.

Is there a way to do this in mySql without the need to break apart Table After Sales in multiple tables, one for each subset (one for service, another for repair, another for battery, etc).

It will certainly work with exploding the AfterSalesPriceList Table, but I am trying to make it what seems to me more logical, ie, have all After Sales prices in a single table.

Thanks

Scenario: simple 4 page web site, 3 with php scripts that pull data from a MySQL database. The site works perfectly locally.  Database and pages ar...More

Scenario: simple 4 page web site, 3 with php scripts that pull data from a MySQL database. The site works perfectly locally.  Database and pages are uploaded to web host 1and1. The localhost, localuser, password parameters are changed according to instructions on the 1and1 site.

Problem:  2 of the php pages work fine, but 1 fails to load, giving the error message that SELECT command denied to user 'dboxxxxxxxxx@xxx.xxx.xxx.xxx' on table 'xxxxxxxx' which is one of the tables in the database.  I'm not sure how it's possible for this to work locally and not remotely.  I tried to grant permissions manually but 1and1 apparently disallows this.

Thanks for any help.

I have got three tables as follows

• achievement id, emp_id,area,dat

• calls_per_day ...More

I have got three tables as follows

• achievement id, emp_id,area,dat

• calls_per_day id, emp_id,noofcalls, hotcalls, hotleads, dat

• sales_statistics_master id,emp_id, target, commitment,mnth, yrs

Now I want to extract the data of the employee from the above three tables about his achievement, noofcalls, hotcalls,hotleads perday against the monthly target and commitment.

I am using the following query.

SELECT  a.area, s.target,s.commitment,c.noofcalls,c.hotcalls,c.hotleads 
FROM   achievement as a, sales_statistics_master as s, calls_per_day as c 
WHERE  a.dat=CURDATE() AND 
       c.dat=CURDATE() AND 
       s.yrs= year(now()) AND 
       a.emp_id=s.emp_id AND
       a.emp_id=c.emp_id

I am validating the existence of a set of field values (Eg: yyy,aaa and ccc) in a table and am using UNION to gather my results, would prefer a more...More

I am validating the existence of a set of field values (Eg: yyy,aaa and ccc) in a table and am using UNION to gather my results, would prefer a more elegant and efficient solution:

TableName: Example_Table

Name | Class

xxx  | CSE <BR>
yyy  | EE  <BR>
zzz  | MECH <BR>
aaa  | CSE  <BR>
bb   | CSE <BR>

My Query:

SELECT  IF(Name  IS NULL,"yyy ","")   
FROM Example_Table where (Name  LIKE '%yyy%') 
      UNION   
(SELECT  IF(Name  IS NULL,"aaa  ","")   
FROM Example_Table where (Name  LIKE '%aaa%')) 
      UNION   
(SELECT  IF(Name  IS NULL,"ccc ","")   
FROM Example_Table where (Name  LIKE '%ccc%'));

I need to know which of the string values I need are not present in the table. The problem is, the table I am using has around thousands of tuples and using UNION seems very costly..

What I am trying to do is get a random image from each user from ...More

What I am trying to do is get a random image from each user from all images.

I have done a select random and a group by. I was told order by rand is slow and have read it is better to do the way below. However this query is painfully slow. Can some one please advice what i am doing wrong and the best way to get a random item for each user.

SELECT im.id, im.title, im.alias_title, im.guid_id, im.hits, al.title, uf.user_name, imu.alias_title,
((ACOS(SIN(53.38946 * PI() / 180) * SIN(geo.latitude * PI() / 180) + COS(53.38946 * PI() / 180) 
* COS(geo.latitude * PI() / 180) * COS((-2.599586 - geo.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)
AS distance, im.date_taken, uf.real_name FROM image im
INNER JOIN (SELECT user_id, alias_title FROM image ORDER BY floor(RAND()*(SELECT count(1) FROM image))) imu ON im.user_id =imu.user_id
INNER JOIN album_image ai ON im.id = ai.image_id
INNER JOIN album al ON ai.album_id = al.id
INNER JOIN user us ON im.user_id = us.id
LEFT JOIN user_flickr uf ON us.flickr_id = uf.id
LEFT JOIN user_site uss ON us.user_site_id= uss.id
LEFT JOIN geolocation geo ON im.geolocation_id = geo.id
Group by imu.user_id

I've been suggested to migrate from the deprecated MySQL to MySQLi or PDO, I finally got around to it as it'll help my project without wasting too m...More

I've been suggested to migrate from the deprecated MySQL to MySQLi or PDO, I finally got around to it as it'll help my project without wasting too much time. I've been reading a lot on some great articles and websites, but I'd like the best suggestion on how to fetch array with PDO, I can do it with a while loop but I dislike it, how would I convert this current code to PDO.

public function User_Details($_iD){
    $_iD = mysql_real_escape_string($_iD);
    $query="SELECT _iD,_iPassword,_iEmail,_iNickname,_iUsername,_iProfilePicture,_iFriendCount FROM users WHERE _iD='$_iD' AND _iStatus='1'";
    $result = mysql_query($query) or die(mysql_error());
    $data = mysql_fetch_array($result) or die(mysql_error());
return $data;
}

I am able with a while loop but as I said, that's not what I'm interested in unless it's a better option.

PDO :

$sql = "SELECT _iD,_iPassword,_iEmail,_iNickname,_iUsername,_iProfilePicture,_iFriendCount FROM users WHERE _iStatus='1'";
    foreach ($db->query($sql) as $row){
        print $row['_iD'] .' - '. $row['_iUsername'] . '<br />';
    }

I'm building an interactive page where a chatbox and a list of items appear.

I intend to update both of them every 30 seconds using JQUERY's...More

I'm building an interactive page where a chatbox and a list of items appear.

I intend to update both of them every 30 seconds using JQUERY's .post() function, but I realize many users keep webpages opened in tabs 24 hours a day, creating a heavy load on the server.

In order to prevent this and to optimize my ressources, I would like the database polling to be executed every 30 seconds ONLY if the user is active on the page; in the passive cases, I would like a 5 minutes polling.

How can I achieve this? (I'm using PHP and MYSQL)

I generate .csv files using a python script by writing a pandas DataFrame to_csv, using utf8 encoding.

consEx.to_csv(o...More

When i am reading some text from an Xml and putting it to the database i am getting a error if the text contains '(apostrophe) . How to overcome thi...More

When i am reading some text from an Xml and putting it to the database i am getting a error if the text contains '(apostrophe) . How to overcome this problem while i am inserting into the DB.

So I have this code:

$arr = array(
  1,2,3,4,5,6
);
$num = count($arr);
$sum = array_sum($arr);
$average = $sum/$num;
foreach($arr a...More

I have a database in MySQL Workbench with fields like (name, lastname, age, address, etc..) and a windows desktop application (a form) in visual stu...More

I have a database in MySQL Workbench with fields like (name, lastname, age, address, etc..) and a windows desktop application (a form) in visual studio with c# where you can insert , search and update. When I insert data from the form and I leave some fields empty, they are saved in the database as blank and I want them to be saved as null. Is there a way to make that happened?

Here is and insert button code:

conn = openconnection.GetConn();

MySqlCommand cmd = new MySqlCommand("INSERT INTO table_name (name,lastname,address, etc...) VALUES (' " + name_textbox.Text + " ',' " + lastname_textbox.Text + " ' etc... );", conn);

cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();

Hello I just started learning php, and i want to write a .txt file content to a mysql table. I tried this code but it doesn't work... any ideas why?...More

Hello I just started learning php, and i want to write a .txt file content to a mysql table. I tried this code but it doesn't work... any ideas why?

Code:

$file = ($upload_dir.$file_name);

$file_open = fopen($file, 'r');
$file_content = fread($file_open, 1000);
fclose ($file_open);


$mysql_insertas = mysql_query("INSERT INTO `test` ('failoTurinys') VALUES ($file_content)");
if (!$mysql_insertas) {
    echo "<font color='red'>FAILED cryyyy</font><br>";
} else {
    echo 'will never happen <br>';
}

I have this table (PERSONS) with 25M rows:

ID int(10) PK
points int(6) INDEX
some other columns

I want to...More

I have this table (PERSONS) with 25M rows:

ID int(10) PK
points int(6) INDEX
some other columns

I want to show the user 4 random rows which are somewhat close to each other in points. After a lot of performing testing I figured this can't be done on the database, since they are made to show precise data and not some random rows with this rowcount. I found this query after some searching and tuning to generate random rows which is impressive fast:

SELECT person_id, points
FROM persons AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(person_id)
                        FROM persons)) AS id)
        AS r2
 WHERE r1.person_id>= r2.id and points > 0
 ORDER BY r1.person_id ASC
 LIMIT 4

So I query this in my PHP file. Which gives me great and fast results (below 0.05 seconds when warmed up). But these rows are really just random (with at least 1 point since the points > 0). I would like to show some rows which are a little bit close, doesn't have to be every time, but let's say I do this query with limit 50 and than select a random row in PHP and the 3 closest rows (based on points) next to it. I would think you would need to sort the result, pick a random row and show the rows after/before it. But i have no idea how I can make this, since I am quite new to PHP.

Anyone suggestions, all feedback is welcome :)

I'm using the MySQL JPrante JDBC River plugin for Elasticsearch to index data from MySQL.

In a record set I have the fields longitude (More

I'm using the MySQL JPrante JDBC River plugin for Elasticsearch to index data from MySQL.

In a record set I have the fields longitude (float), latitude (float), pin (POINT), how I can preset them to be detected as a geo_point in elasticsearch?

I have tried with :

curl -XPUT 'localhost:9200/_river/mysql_jdbc_river/_meta' -d '{
"type": "jdbc",
"jdbc": {
    "strategy": "simple",
    "driver": "com.mysql.jdbc.Driver",
    "url": "jdbc:mysql://localhost:3306/somedatabase",
    "user": "someuser",
    "password": "somepassword",
    "sql": "SELECT * FROM activities",
    "poll": "30m",
    "rounding": null,
    "scale": 0,        
    "autocommit": false,
    "fetchsize": 10,
    "max_rows": 0,
    "max_retries": 3,
    "max_retries_wait": "10s",
    "digesting": true,
    "acksql": null,
    "acksqlparams": null
},
"index": {
    "index": "jdbc",
    "type": "jdbc",
    "bulk_size": 100,
    "max_bulk_requests": 30,
    "index_settings": null,
    "type_mapping": {
        "location_mapping" : { 
           "properties" : {
                "pin" : {
                    "type" : "geo_point", 
                    "lat_lon": true, 
                    "store" : "yes"
                 }
            }       
        }
    },
    "versioning": false,
    "acknowledge": false
}
}'

[UPDATE 1]

heres the error with mapping name change to jdbc as described by javanna:

[2013-05-21 13:20:58,256][WARN ][river.jdbc ] [node-0] [jdbc][mysql_jdbc_river] failed to create index [jdbc], disabling JDBC river... org.elasticsearch.index.mapper.MapperParsingException: Failed to parse mapping definition at org.elasticsearch.index.mapper.DocumentMapperParser.extractMapping(DocumentMapperParser.java:245) at org.elasticsearch.index.mapper.DocumentMapperParser.parse(DocumentMapperParser.java:155) at org.elasticsearch.index.mapper.MapperService.parse(MapperService.java:383) at org.elasticsearch.index.mapper.MapperService.parse(MapperService.java:379) at org.elasticsearch.cluster.metadata.MetaDataMappingService$4.execute(MetaDataMappingService.java:310) at org.elasticsearch.cluster.service.InternalClusterService$2.run(InternalClusterService.java:229) at org.elasticsearch.common.util.concurrent.PrioritizedEsThreadPoolExecutor$TieBreakingPrioritizedRunnable.run(PrioritizedEsThreadPoolExecutor.java:95) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722) Caused by: org.elasticsearch.common.jackson.core.JsonParseException: Unexpected character ('=' (code 61)): was expecting a colon to separate field name and value at [Source: {jdbc={properties={pin={lat_lon=true, type=geo_point}}}}; line: 1, column: 7] at org.elasticsearch.common.jackson.core.JsonParser._constructError(JsonParser.java:1378) at org.elasticsearch.common.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:599) at org.elasticsearch.common.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:520) at org.elasticsearch.common.jackson.core.json.ReaderBasedJsonParser.nextToken(ReaderBasedJsonParser.java:616) at org.elasticsearch.common.xcontent.json.JsonXContentParser.nextToken(JsonXContentParser.java:50) at org.elasticsearch.common.xcontent.support.XContentMapConverter.readMap(XContentMapConverter.java:70) at org.elasticsearch.common.xcontent.support.XContentMapConverter.readOrderedMap(XContentMapConverter.java:60) at org.elasticsearch.common.xcontent.support.AbstractXContentParser.mapOrdered(AbstractXContentParser.java:126) at org.elasticsearch.index.mapper.DocumentMapperParser.extractMapping(DocumentMapperParser.java:243) ... 9 more [2013-05-21 13:20:58,264][INFO ][cluster.metadata ] [node-0] [_river] update_mapping [mysql_jdbc_river] (dynamic)

How can I get the 2nd post from each category in php mysql?

If I have a table as following:

post_id  |   cate_id
-----------...More

I am trying to use the Sphinx main + delta system with Sphinx. I got everything working except for the Cron job keeps on saying WARNING: no su...More

So I'm trying to export a MySQL table into CSV. I'm using this query:

SELECT * FROM business WHERE id > 0 AND id <= 20000 INTO...More