DBase & PHP

YoungManRiver · Jul 20, 2007

All,

Working on PHP script to open existing "Customer Add" function to pull data from existing "ACT" .dbf file.

Here is code so far:

Code:

<?php
   $af = 'c:\myact.dbf';
   $db = dbase_open($af, 0);
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      echo "G rec cnt => $record_numbers<br>";
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
             echo "Member #$i: " . trim($row['name']) . "<br>";
      }
   }
   dbase_close($db);
?>

What I can't do is simulate the following SQL command:

Code:

SELECT DISTINCT company FROM file;

So I can propogate PHP var of $comp_list to build PHP/HTML <SELECT>/<OPTION> drop-down to select the info from.

Count, from first echo, is accurate 6000+ records, but cannot figure out how to parse the $row array to get right column and value. Also tried using the "dbase_get_header_info" function, but same array parsing problem. Know the data is there, just need to know how to get it.

Here is PHP/HTML file to display:

Code:

<html>
<head>
<title>MyOps System - Invoice</title><STYLE TYPE="text/css">
   <!-- BODY,  P {color: black; font-family: verdana; font-size: 10 pt}
        H1     {color: black; font-family: arial; font-size: 12 pt}  -->
<style type="text/css">
body {background-color: #ffffff; color: #000000;}
body, td, th, h1, h2 {font-family: sans-serif;}
pre {margin: 0px; font-family: monospace;}
a:link {color: #000099; text-decoration: none; background-color: #ffffff;}
a:hover {text-decoration: underline;}
table {border-collapse: collapse;}
.center {text-align: center;}
.center table { margin-left: auto; margin-right: auto; text-align: left;}
.center th { text-align: center !important; }
td, th { border: 1px solid #000000; font-size: 75%; vertical-align: baseline;}
# td.bd, th.bd { border: 1px solid #000000; font-size: 75%; vertical-align: baseline;}
h1 {font-size: 150%;}
h2 {font-size: 125%;}
.p {text-align: left;}
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.h {background-color: #9999cc; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
.vr {background-color: #cccccc; text-align: right; color: #000000;}
img {float: right; border: 0px;}
hr {width: 600px; background-color: #cccccc; border: 0px; height: 1px; color: #000000;}
</style>
</head>

<BODY>
   <FORM METHOD="POST" ACTION="">
   <TABLE BORDER=2 CELLPADDING=5 WIDTH=400 align=center BGCOLOR="#F0F8FF" >
       <TR>
       <TD class=bd>
       <TABLE BORDER=0 CELLPADDING=5 WIDTH=375 align=center BGCOLOR="#F0F8FF" >
           <TR>
           <TD class=bd colspan=6 ALIGN=center VALIGN=bottom>
               <H1>MyOps System</H1>
               Company Add Screen
           </td>
           </tr>

           <tr>
           <td width=50 class=bd align=right>Company:</td>
           <td width=300 colspan=3 class=bd align=left>
               <SELECT NAME="inv_cmp">
               <?php echo $comp_list; ?>
               </SELECT>
           </td>
           </tr>

           <tr>
           <td align=center colspan=6>
               <INPUT TYPE="submit" NAME="submit" VALUE="Submit">
           </td>
           </tr>
       </TABLE>
       </td>
       </TR>
   </TABLE>
   </FORM>
</BODY>
</HTML>

Thanks!

YMR

YoungManRiver · Jul 20, 2007

Latest Code:

Here is where I'm at with this. Getting errors from the "array_search" and "array_key_exists" functions. Wondering if it is something in the PHP.ini file?

Code:

<?php
   $af = 'c:\ACT\myact.dbf'; 
   $db = dbase_open($af, 0);
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      echo "G rec cnt => $record_numbers<br>";
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
          foreach ($row as $key => $val) {
             $ii=0;
             if ($key=='COMPANY') {
                $ii=$ii+1;
		$key = array_search($val,$outline);
		if (!array_key_exists($key,$outline)) {
                   $ii=$ii+1;
                   $outline[ii] = $val;
                }
             }    /* end if($key)   */
          }       /* end foreach    */
      }           /* end for($i)    */
   }              /* end if($db)    */
   dbase_close($db);
   $comp_list="";
   $ii=0;
   foreach ($outline as $key => $val) {
      $ii=$ii+1;
      $comp_list .= "<option value=$ii>$val</option>";
   }
?>

These functions are supposed to test to see if the value is already in the array.

Need any and all suggestions!

YMR

YoungManRiver · Jul 20, 2007

Errors:

All,

The errors I'm getting are:

Code:

Warning: array_search() [function.array-search]: Wrong datatype for second argument in D:\Local Files\Web Projects on Server\My Software\My Ops\c_add.php on line 14

Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in D:\Local Files\Web Projects on Server\My Software\My Ops\c_add.php on line 15

Maybe this helps?

YMR

YoungManRiver · Jul 20, 2007

Lastest Code Again:

All,

This works, sorta. Is error free but still has dup names.

Code:

<?php
   $af = 'c:\ACT\myact.dbf';
   $db = dbase_open($af, 0);
   $outline = array();
   $comp_list="";
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      echo "G rec cnt => $record_numbers<br>";
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
          foreach ($row as $key => $val) {
             $ii=0;
             if ($key=='COMPANY') {
                if (!array_search($key, $outline)) {
                    $k++;
                    $outline = array("rowno" => $i, "comp" => $val);
      		    $comp_list .= "<option value=$k>$val</option>";
                }
             }    /* end if($key)   */
          }       /* end foreach    */
      }           /* end for($i)    */
   }              /* end if($db)    */
   dbase_close($db);
?>

May have to push into temp MySQL table to fix dup name problem.

If you have a better way let me know!

YMR

jpadie · Jul 21, 2007

i think you will be getting all the deleted data too, unless you do dbase_pack() on the resource. this could be your issue.

otherwise i do not think that the php dbase wrapper supports search the db, so you will have to do so programmatically.

you're current method, I don't think, will work as you are overwriting $outline each loop and also you will end up trying to use array_search on a multidimensional array. you could, perhaps fix it by changing the line to this

Code:

$outline[$i] =  $val;

but i think that the method below might work better: let's assume that you want to dedupe on Company Name

Code:

<?php
   $af = 'c:\ACT\myact.dbf';
   $db = dbase_open($af, 0);
   $companyNames = array();
   $comp_list="";
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      echo "G rec cnt => $record_numbers<br>";
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
         [red] 
		if (in_array($row['COMPANY'], $companyName)){
		  	//skip output
		} else {
			//add to array
			$companyNames[] = $row['COMPANY'];[/red]
			$comp_list .= "\t<option value=\"$i\">$row[COMPANY]</option>\r\n";
		} //end of dedupe IF [/red]

      }           /* end for($i)    */
   }              /* end if($db)    */
   dbase_close($db);
?>

YoungManRiver · Jul 22, 2007

Fixed:

Got this fixed with the following code:

Code:

<?php
   $af = 'C:\ACT\myact.dbf';        //LapTop
   $db = dbase_open($af, 0);
   $outline[] = array();
   $comp_list="";
   $k = 0;
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      echo "G rec cnt => $record_numbers<br>";
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
          foreach ($row as $key => $val)  {
             if ($key == 'COMPANY') {
                if (!array_search($val, $outline[$k])) {
                    $k++;
                    $outline[$k] = array("rowno" => $i, "comp" => $val);
                }
             }    /* end if($key)   */
          }       /* end foreach    */
      }           /* end for($i)    */
   }              /* end if($db)    */
   dbase_close($db);
   asort($outline['comp']);
?>

Thanks all!

YMR

jpadie · Jul 23, 2007

if you do not need the row number you can omit the $k and $k++. just call

Code:

$outline[] = array("rowno" => $i, "comp" => $val);

YoungManRiver · Jul 25, 2007

jpadie,

Yes technically I do not need the rowno, but since I want to reference the dBase to input into MySQL after clicking the "Submit" button on my page, then yes I do need the rowno for now I can go straight to it in dBase.

Thanks for your help though!

Sorry I didn't share that little design insight up front.

YMR

YoungManRiver · Jul 26, 2007

Still not Working Just Right!

All,

When I finally got all this working and started testing, I found I was still getting duplicate values. This is supposed to be detected by taking a new value, doing a lookup on the "outline" array to see if the value is already in the array, with either "in_array" or "array_search", but neither is working right.

So thinking, maybe it was a padding issue, I added the "trim" to my "$val" to make sure of no leading or trailing padding. No luck. Maybe I don't have the lookup defined right for my "outline" array, but don't know?

I can eliminate the dups if I insert all into a temp MySQL table and run a "SELECT DISTINCT" on that, but this whole thing is suppose to eliminate the need to use that extra processing power.

Does anyone know how to solve my problem.

I can submit samples so you can duplicate the problem.

YMR

YoungManRiver · Jul 26, 2007

Fixed Finally,

All,

To get this to work right had to add second array check. Found minor error in first array declaration, which was keeping the first array lookup from working correctly, but do not know why the second is required.

Code follows:

Code:

<?php
   // Connect to MySQL   
   require('db_connect.php');
   // Load current companies into array
   $sql = "SELECT cli_cnm FROM client ORDER BY cli_cnm;";
   $result = mysql_query($sql);
   while ($row = mysql_fetch_assoc($result)) {
      $rw_vl_cl[] = trim($row['cli_cnm']);
   }
   // init for ACT database load
   $af = 'C:\ACT\myact.dbf';        //LapTop
   $db = dbase_open($af, 0);
   $k = 0;
   $outline[$k] = array();
   $tmpline = array();
   $comp_list="";
   if ($db) {
      $record_numbers = dbase_numrecords($db);
      // Load ACT database into array
      for ($i = 1; $i <= $record_numbers; $i++) {
          $row = dbase_get_record_with_names($db, $i);
          foreach ($row as $key => $val)  {
             $val = trim($val);
             if ($val == '' || $val == NULL) {
             	continue;
             }
             if ($key == 'COMPANY') {
                // Eliminate current companies from array
        	if (!in_array($val, $rw_vl_cl)){
                   // Eliminate duplicate from array
        	   if (!in_array($val, $outline[$k])){
                      $k++;
                      $outline[$k] = array("comp" => $val,"rowno" => $i);
		   }
                }
             }    /* end if($key)   */
          }       /* end foreach    */
      }           /* end for($i)    */
   }              /* end if($db)    */
   dbase_close($db);
   sort($outline);
   for ($i = 1; $i <= $k; $i++) {
       $rw = $outline[$i]['rowno'];
       $vl = $outline[$i]['comp'];
       // Second elimination of duplicates
       if (!in_array($vl, $tmpline)){
       	  echo "Val => $vl <=  Row => $rw <= <br>";
       	  $comp_list .= "<option value=$rw>$vl</option>";
          $tmpline = array($vl);
       }
   }
?>

Take a little processing time, but much faster than a MySQL load, then call.

YMR

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

DBase & PHP

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

jpadie

Technical User

YoungManRiver

IS-IT--Management

jpadie

Technical User

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

DBase &amp; PHP

IS-IT--Management

IS-IT--Management

IS-IT--Management

IS-IT--Management

Technical User

IS-IT--Management

Technical User

IS-IT--Management

IS-IT--Management

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor

DBase & PHP