HELP ME ON: Selecting all the data from one column of a table in mysql database

[zamzam]

Hello Everyone,
May some one help me with sql statement and php on how to select all the data from one column of table. I have tried with a code below but it is not working. I want to be selecting some data (all) data from one column table that meets some condition, then use selected data to update other table.
Php and sql are below;
Your help will be highly appreciated.

<?php

// selecting all the records where uploaded is YES
$query="SELECT * FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
$result=mysql_query($query);

$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
// Getting the Travel numbers that meets the conditions, so that records
//that that have same Travel number as selected can be updated using
// update statement below.
$zra=mysql_result($result,$i,"TravelNumber");

$i++;
}

$mysql_query ="UPDATE traveldetails SET BatchNumber='$batchnumber'where uploaded='NO'AND TravelNumber='$zra";
mysql_query($mysql_query) or die('Error, update query failed');




?>

[dg_den_golotyuk]

It is not a great problem. Try making in such a way:

...
$res = mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'");
while ( $item = mysql_fetch_array($res) )
{
    $current_travel_number = $item['TravelNumber'];
}

... 

[dg_den_golotyuk]

Also the update statement you should move to the cycle body - cause you have to do updates for all rows, you are getting

[zamzam]

Hello dg_den_golotyuk
what do you mean by saying the update statement should move to the cycle body ?
Give me some examples if it possible.
thanks.

[dg_den_golotyuk]

I think, that your script will look like:

<?php

// selecting all the records where uploaded is YES
$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
$result=mysql_query($query);

while ($item = $mysql_fetch_array($result)) {
$zra=itemp['TravelNumber'];

if ( $zra )
{
mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'");
}

}

?>

[zamzam]

I have tried above code, it is not updating the other table (Traveldetails Table)

[dg_den_golotyuk]

Think there is such DATA in theese tables, so there is nothing to update. Try to add a line before 'if ( $zra )': echo $zra; - will look what is going wrong

[zamzam]

It is not displaying records, but I can check from table that data is there.

[zamzam]

when I echo $zra; It is not displaying records, but I can check from table that data is there.

[siteguru]

Which means that the SELECT query is not returning any records. Probably because $batchnumber has no value?

[dg_den_golotyuk]

Which means that the SELECT query is not returning any records. Probably because $batchnumber has no value?

You absolutely right!!! $batchnumber - what is this variable for?

[dg_den_golotyuk]

Try this:

<?php

echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

?>

[zamzam]

$batchnumber has value. becuase in the first table(payment) is showing that $batchnumber has value

[dg_den_golotyuk]

So there are no rows, that are returning on such query.
Insert such code after $res = mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"):

echo mysql_num_rows($res) + 0;

It will show us the number of returned resulted rows

[zamzam]

When I use <?php

echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

?>
I get something similar or output in the broswer as shown below.
SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='231'

[dg_den_golotyuk]

When I use <?php

echo "SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";

?>
I get something similar or output in the broswer as shown below.
SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='231'

so $batchnumber' has the value. That is not a problem. Try the feature with mysql_num_rows(...) now and post what is it outputting

[zamzam]

When I use mysql_num_rows(...) get the following out put 0

[dg_den_golotyuk]

When I use mysql_num_rows(...) get the following out put 0

That means no rows matching your criteria with specified input parameters. I'm not actually getting what are you trying to do. The script i working fine - there are just no results on this query. Maybe you should try changing query somehow. Try to explain - what are you trying to do

[zamzam]

I have two tables. Payment and Travel details. I want when someone (User or admin) wants to generate a batch of records and then export data in csv. Iam able to generate a batch of records using payment table details with out a problem. Now the details that are in Traveldetails table and payment table are related. So what I want to achieve is like this. Since Iam generating a batch number which appended to the records in that batch using payment table, then I want same batch number to be appended to records with same Travel number in traveldetails table. So that is what I what to achieve. If you want some details iam reay to give. Your help will be highly appreciated.
The code is below and table sql
<?php
include ('../conn.php');


$ebatchh=mysql_query("select BatchNumber from payments where uploaded= 'YES' ORDER BY BatchNumber DESC LIMIT 1")or die("<center>
<font face=\"arial\" size=\"2\">Error!! Select Operation Failed. Reason:
".mysql_error()."</font></center>");
$row = mysql_fetch_object($ebatchh);
$embbb= $row->BatchNumber;


$batchnumber = $embbb+1;

$mysql_query ="UPDATE payments SET BatchNumber='$batchnumber'where uploaded ='NO' ORDER BY Id ASC LIMIT 20";
mysql_query($mysql_query) or die('Error, update query failed');



$select = "SELECT EmployeeNumber , ApplicationDate,TravelNumber ,Transactioncode,TotalAmount,BatchNumber FROM payments where uploaded='NO' and BatchNumber='$batchnumber'";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

for ($i = 0; $i < $fields; $i++) {
//$header .= mysql_field_name($export, $i) . "\t";
}



while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '' .$value. ',' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
header("Cache-control: private");
header("Content-type: application/force-download");
header("Content-Disposition: attachment; filename=TRVP$batchnumber.csv");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";


$mysql_query ="UPDATE payments SET uploaded='YES'where uploaded ='NO' ORDER BY Id ASC LIMIT 20";
mysql_query($mysql_query) or die('Error, update query failed');


//$ebtravel=mysql_query("select TravelNumber from payments where uploaded='YES' and BatchNumber ='$batchnumber'")or die("<center>
// <font face=\"arial\" size=\"2\">Error!! Select Operation Failed. Reason:
// ".mysql_error()."</font></center>");
//$row = mysql_fetch_object($ebtravel);
//$emtravel= $row->TravelNumber;

//$res = mysql_query("SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'");
//while ( $item = mysql_fetch_array($res) )

// $current_travel_number = $item['TravelNumber'];












$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
$result=mysql_query($query);

while ($item = $mysql_fetch_array($result)) {
$zra=$item['TravelNumber'];

if ($zra)
echo "$zra";
{
mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'");
}

}

?>


SQL TABLE

Table structure for table `payments`
--

CREATE TABLE `payments` (
`Id` int(10) NOT NULL auto_increment,
`EmployeeNumber` varchar(20) NOT NULL default '',
`ApplicationDate` date NOT NULL default '0000-00-00',
`TravelNumber` varchar(10) NOT NULL default '',
`Transactioncode` varchar(30) NOT NULL default '',
`TotalAmount` int(30) NOT NULL default '0',
`BatchNumber` varchar(10) NOT NULL default '',
`uploaded` varchar(3) default 'NO',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `payments`
--

-- --------------------------------------------------------

--
-- Table structure for table `traveldetails`
--

CREATE TABLE `traveldetails` (
`Id` int(10) NOT NULL auto_increment,
`EmployeeNumber` varchar(10) NOT NULL default '',
`ApplicationDate` date NOT NULL default '0000-00-00',
`NRC` varchar(20) NOT NULL default '',
`TravelNumber` varchar(20) NOT NULL default '',
`SAPAccount` varchar(20) NOT NULL default '',
`Description` text NOT NULL,
`TransactionType` varchar(20) NOT NULL default '',
`TotalAmount` int(20) NOT NULL default '0',
`BatchNumber` int(20) NOT NULL default '0',
`Payable` varchar(20) NOT NULL default '',
`uploaded` varchar(3) NOT NULL default 'NO',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `traveldetails`
--

[dg_den_golotyuk]

Please add this code directly under include - in the top of the scipt. Run it and say, what does it outputs.

$ebatchh=mysql_query("select BatchNumber from payments where uploaded= 'YES' ORDER BY BatchNumber DESC LIMIT 1");
$row = mysql_fetch_object($ebatchh);
echo $embbb= $row->BatchNumber;
echo ' : ';
echo $batchnumber = $embbb+1;

$mysql_query ="SELECT * FROM payments WHERE uploaded ='NO' ORDER BY Id ASC LIMIT 20";
$q = mysql_query($mysql_query);
echo '<br> Number of rows mathcing you query: ';
echo mysql_num_rows($q);

[zamzam]

Below is the out put Iam getting

226 : 227
Number of rows mathcing you query: 7

[dg_den_golotyuk]

now please change '$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
' to 'echo " - " . $query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'";
'

what will be the output?

[zamzam]

This is the output when I use the above code

Number of rows mathcing you query: 7 - SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='227'

[dg_den_golotyuk]

Let's now remake this block:

$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"; 
$result=mysql_query($query); 

while ($item = $mysql_fetch_array($result)) { 
$zra=$item['TravelNumber']; 

if ($zra)
echo "$zra"; 
{ 
mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'"); 
} 

} 

change it to:

$query="SELECT TravelNumber FROM payments where uploaded='YES' and BatchNumber ='$batchnumber'"; 
$result=mysql_query($query);

echo '<br> Total numbers:';
echo mysql_num_rows($result);
echo '<br>Iterating: <br>';

while ($item = $mysql_fetch_array($result)) { 
echo 'zra = ' . $zra=$item['TravelNumber'];

if ($zra)
echo "$zra"; 
{ 
mysql_query("UPDATE traveldetails SET BatchNumber='$batchnumber' where uploaded='NO' AND TravelNumber='$zra'"); 
echo ' ... this one updated successfully...';
} 

echo '<br>'; 
} 

[zamzam]

Iam getting the output below but the script is not updating the Traveldetails table
Total numbers:4
Iterating