PHP - Importing Csv Into Mysql Through Php Not Working Right

Hi Guys

Just need some advice to go in the right direction.

I'm working on a csv upload script (part of a bigger thing i'm building), so i read in the csv to a multipdimensional array and then build a query that inputs all rows in one query - i read this is the most efficient way to import multiple rows of data at once(rather than multiple insert statements). Just for illustration here's the code i use to build the query so you understand what i'm on about:

Code: [Select]
$sql = "INSERT INTO teams (company, teamname, teamnum, amountraised, country, president) VALUES ";

// $rows is a count of the rows in the csv

for($i=1; $i<$rows; $i++){

$sql.="('{$myarray[$i][0]}','{$myarray[$i][1]}','{$myarray[$i][2]}','{$myarray[$i][3]}','{$myarray[$i][4]}','{$myarray[$i][5]}')";
echo $i . "<br/>";

if($i >= 1 && $i < $rows - 1)
{
$sql.= ",";
}
}

Anyway, the issue is that one of the fields("teamnum") needs to be unique - so i've set this as unique on the table in mysql.

But when i run my query it doesn't import anything if one of the records isn't unique. What i really want is for it to import the ones it can and catch the ones it cant import to present to the user.

So my question is - to acheive the above would i need to rewrite the query so that it inserts each row one at a time, instead of all together?

Or can someone point me in the right direction for a better solution?

Probably something very simple i've missed i am sure...

Thanks chaps!

does anyone know who to resolve this issue of importing a CSV file from excel into sql? I get this error when I do.

LOAD DATA LOCAL INFILE  '/tmp/phpq2aAbU' INTO TABLE  `Events` FIELDS TERMINATED BY  ',' ENCLOSED BY  '\\"' ESCAPED BY  '\\\\' LINES TERMINATED BY  '\r\n'

When I echo the POST, it echoes the correct value.
The MySQL portion seems to just ignore it all together.
I've tried changing the dropdown option to just a text field, same thing occurred.
I have text fields right above this particular one that update just fine with the SAME exactly scripting.
if POST, update query, done. Works.

This one for some reason will not.

MySQL portion:
if ($_POST['bUpdate']){
mysql_query("UPDATE `Patients` SET `b` = '$_POST[bUpdate]' WHERE `id` = '".$_GET['id']."'");
}
echo $_POST['bUpdate'];

Form Portion:
Code: [Select]
    <tr onmouseover="color(this, '#baecff');" onmouseout="uncolor(this);">
    <td width="310" colspan="2" align="center"><span class="fontoptions">Postcard Status </span><br />
<?
if ($data['b'] == 1){
echo '<select name="bUpdate"><option value="1" selected>Yes</option><option value="0">No</option></select>';
} else {
echo '<select name="bUpdate"><option value="1">Yes</option><option value="0" selected>No</option></select>';
}

?>
</td>
    </tr>

I'm attempting to import a mysql database backup via php unfortunately I keep getting this error:

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 'SOURCE file.sql' at line 1

Code:

mysql_query("SOURCE file.sql") or die (mysql_error());


I've researched this online and this seems to be the way everyone suggests to use, unfortunately it isn't working. If it helps here is the first 4 lines of file.sql as that may be the problem for all I know:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--

Haven't really used traits much and am confused whether to import other classes in the trait or the class that uses the trait (might also be an annotation question but not sure).

Must import statements (i.e. use Foo\Bar;) be included in the class or can they be included in just the trait which is used by a class?  Does it depend and if so on what?  Does having one class use another class impact the decision?  Is it okay for one trait to use another and another trait to just use the first trait or should it use both?

What is odd is I apparently must (to not get an error) import ApiProperty to the trait but doing so is not required for the class, and must import Groups, SerializedName, and Mapping in the class but doing so is not required for the trait.

Instead of just a yes/no answer, would appreciate an explanation why it is what it is.

Thanks!

<?php

namespace App\Entity\Document;
use ApiPlatform\Core\Annotation\ApiResource;
use Doctrine\ORM\Mapping as ORM;
use App\Entity\MultiTenenacy\PublicIdTenantTrait;

// Must Groups, SerializedName, Owner be imported in this class or just PublicIdTenantTrait 
// use Symfony\Component\Serializer\Annotation\Groups;
// use Symfony\Component\Serializer\Annotation\SerializedName;use 
// use App\Entity\Owner\Owner;

/**
 * @ORM\Entity()
 * @ApiResource()
 */
class Document
{
    use PublicIdTenantTrait;
}

 

<?php
namespace App\Entity\MultiTenenacy;

use ApiPlatform\Core\Annotation\ApiProperty;
use Doctrine\ORM\Mapping as ORM;

trait PublicIdTenantTrait
{
    use PublicIdExtendedTenantTrait;
    
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ApiProperty(identifier=false)
     * @ORM\Column(type="integer")
     */
    private $id;

    public function getId(): ?int
    {
        return $this->id;
    }
}

 

<?php
namespace App\Entity\MultiTenenacy;

use ApiPlatform\Core\Annotation\ApiProperty;
use Symfony\Component\Serializer\Annotation\Groups;
use Symfony\Component\Serializer\Annotation\SerializedName;
use Doctrine\ORM\Mapping as ORM;

trait PublicIdExtendedTenantTrait
{
    use TenantTrait;

    /**
     * @ORM\Column(type="integer")
     * @ApiProperty(identifier=true)
     * @Groups({"tenant_entity:read"})
     * @SerializedName("id")
     */
    private $publicId;

    public function getPublicId(): ?int
    {
        return $this->publicId;
    }

    public function setPublicId(int $publicId): PublicIdTenantInterface
    {
        $this->publicId = $publicId;

        return $this;
    }

    public function generatePublicId(): PublicIdTenantInterface
    {
        $this->getOwner()->setPublicId($this);

        return $this;
    }

    public function getPublicIdTag(): string
    {
        return lcfirst((new \ReflectionClass($this))->getShortName()).'Id';
    }
}

 

<?php
namespace App\Entity\MultiTenenacy;

use Doctrine\ORM\Mapping as ORM;
use App\Entity\Owner\Owner;

trait TenantTrait
{
    /**
     * inversedBy performed in child where required
     * @ORM\ManyToOne(targetEntity=Owner::class)
     * @ORM\JoinColumn(nullable=false, onDelete="CASCADE")
     */
    private $owner;

    public function getOwner(): ?Owner
    {
        return $this->owner;
    }

    public function setOwner(?Owner $owner): TenantInterface
    {
        $this->owner = $owner;

        return $this;
    }
}

 

Hello I am tring to import a .txt file into an array. I am stuck will somone please help me.
?<php
$fp = fopen("ArrayTestFile.txt", 'wb');
$X = fread("ArrayTestFile.txt" 'w');
for ($j = 0 ; $j < 6 ; ++$j)
{
$written = fwrite($fp, "ArrayTestFile.txt");
if ($written == FALSE) break;
}

print    $fp[0] . " " . $fp[1] . " " . $fp[2] . " " . $fp[3] . " " . $fp[4];
fclose($fp);
?>

I have a site where I have an admin panel set up to import an XML data feed into a database, but it's a large import and it takes a lot of time to update.  I'm looking to upgrade the admin panel and offer the ability to be able to see the number of records as they're being imported instead of it hanging there until the query is completed.  The tough part about a progress bar is obviously you have to know the # of items being imported beforehand, and that varies each time the feed is downloaded.  So I'm just trying to get some suggestions from someone who may have written something similar.

Thanks in advance!

I need to write a code were users can login to his/her email account from my site to import contacts to be selected and used. I know you need to use api's for this. Has anyone done this before who can give me some useful tips?

Hello,
I've got two questions. Users can fill in a form with information that has to be in the following format:
Value 1, Value 2, Value 3, Value 4 Value 1, Value 2, Value 3, Value 4 Now I want to return an error if ANY line isn't in that format. For example when it's like this:
Value 1, Value 2, Value 3, Value 4 Value 1, Value 3, Value 4 I've got no clue how to do that...

The second question is, how can I get a specific line of the posted information? I've already gotten an answer on how to count those lines, which is working. But I'm going to insert them into the database using a for loop, so I need to insert one row at a time.

Thanks for your time and help,
Chris

I need someone to look over this and just see if they can see why this won't work anymore?

It worked when the site was in development but when it came down to the day it's not working.

Basically it reads a CSV and inserted the driver into a Quali or Results table in the correct order but it's not working anymore?

If someone could have a read over it and suggest what might be going wrong that would be great.

Edit: It basically just added the same driver 24 times in the table.

Code: [Select]
Public Function ProcessUpload($UR, $RaceID, $RaceName) {
Global $MDB;
// Configuration - Your Options
  $allowed_filetypes = array('.csv'); // These will be the types of file that will pass the validation.
  $max_filesize = 524288; // Maximum filesize in BYTES (currently 0.5MB).
  $upload_path = './resultsfiles/'; // The place the files will be uploaded to (currently a 'files' directory).

$filename = $_FILES['userfile']['name']; // Get the name of the file (including file extension).
$ext = substr($filename, strpos($filename,'.'), strlen($filename)-1); // Get the extension from the filename.

If (strpos($filename, "Quali") == True) {
$tbl_name = "QualiPositions";
$FN = " - Qualifying";
} Else {
$tbl_name = "RacePositions";
$FN = " - Race";
}

$SQL = "SELECT count(*) FROM $tbl_name WHERE RaceID = $RaceID";
$Count = $MDB->prepare($SQL);
$Count->execute();
$Count = $Count->fetchColumn();

If ($Count == 24) {
$Msg = "<div class='error'>Error: We already have 24 Results for this Session.</div>";
}

// Check if the filetype is allowed, if not DIE and inform the user.
if(!in_array($ext,$allowed_filetypes))
  $Msg = "<div class='error'>Error: The file you attempted to upload is not allowed.</div>";

// Now check the filesize, if it is too large then DIE and inform the user.
if(filesize($_FILES['userfile']['tmp_name']) > $max_filesize)
  $Msg ="<div class='error'>Error: The file you attempted to upload is too large.</div>";

// Check if we can upload to the specified path, if not DIE and inform the user.
if(!is_writable($upload_path))
  $Msg ="<div class='error'>Error: You cannot upload to the specified directory, please CHMOD it to 777.</div>";

If (strpos($Msg, "Error:") == False) {

// Upload the file to your specified path.
$filename = str_replace(" ", "", $RaceName)."$FN.csv";
if (move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_path . $filename)) {
    $FilePath = $upload_path . $filename;
if (($handle = fopen($FilePath, "r")) !== FALSE) {
$Pos = 0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
       
        for ($c=0; $c < $num; $c++) {
        $Pos++;
        If ($Pos <= 24) {
            $Driver = explode(" ", $data[$c]);
            $FName = $Driver[0];
            $LName = $Driver[1]." ".$Driver[2]." ".$Driver[3];
            $STH = $MDB->query("SELECT * FROM Drivers WHERE FirstName = '$FName' AND LastName = '$LName'");
$STH->setFetchMode(PDO::FETCH_OBJ);
while($row = $STH->fetch()) {
$DriverID = $row->DriverID;
$ChassisID = $row->ChassisID;
$EngineID = $row->EngineID;
}
$STH = $MDB->prepare("INSERT INTO $tbl_name(RaceID, DriverID, ChassisID, EngineID, Position, DSQ) VALUES (:RID, :DID, :ChaID, :EngID, :Pos, :DSQ)");
$STH->execute(array('RID' => $RaceID, 'DID' => $DriverID, 'ChaID' => $ChassisID, 'EngID'=>$EngineID, 'Pos'=>$Pos, 'DSQ' => 0));
$Msg = "<div class='team_confirm'>The Results have been uploaded for the $RaceName</div>";
} Else {
If ($tbl_name == "RacePositions") {
$Driver = explode(" ", $data[$c]);
            $FName = $Driver[0];
            $LName = $Driver[1]." ".$Driver[2]." ".$Driver[3];
            $STH = $MDB->query("SELECT * FROM Drivers WHERE FirstName = '$FName' AND LastName = '$LName'");
$STH->setFetchMode(PDO::FETCH_OBJ);
while($row = $STH->fetch()) {
$DriverID = $row->DriverID;
}

$sql = "UPDATE RacePositions SET FastLap = 'Yes' WHERE RaceID=? AND DriverID = ?";
$q = $MDB->prepare($sql);
$q->execute(array($RaceID, $DriverID));
}
}
        }
    }
    fclose($handle);
}
} else {
     $Msg = 'There was an error during the file upload.  Please try again.'; // It failed :(.
}
}

Return $Msg;
}


That is the code, the CSV is basically like this:
Code: [Select]
Lewis Hamilton,
Jenson Button,
Romain Grosjean,
Michael Schumacher,
Mark Webber,
Sebastian Vettel,
Nico Rosberg,
Pastor Maldonado,
Nico Hulkenberg,
Daniel Ricciardo,
Jean-Eric Vergne,
Fernando Alonso,
Kamui Kobayashi,
Bruno Senna,
Paul di Resta,
Felipe Massa,
Kimi Raikkonen,
Heikki Kovalainen,
Vitaly Petrov,
Timo Glock,
Charles Pic,
Sergio Perez,
Pedro de la Rosa,
Narain Karthikeyan
Thanks for any help!

This topic has been moved to Miscellaneous.

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

hello,
here is my query


$this->sql_query = "SELECT * FROM ".$this->classifieds_table."
WHERE category ".$this->in_statement." AND live=1 ";

the above works....but when I add {AND location = "".$_COOKIE["State"]."\"} - it doesnt work

no matter what location equals it wont read query. as soon as i remove the extra AND it works.


help please

I know this is probably something very dumb but I am having trouble with my if else. I have a sql query that looks for a record and if nothing is found it echos a warning.

Code: [Select]
<?php

$sql7="SELECT * FROM `$tbl_name2` WHERE Mac = '$Mac' AND Type = '$Type'"; 
$result7=mysql_query($sql7);

  if($result7)
{
while($row=mysql_fetch_array($result7))
echo
"The phone you are using with mac $row[0] $row[1] is a Cisco IP Phone! <br>" ;
}
else 
{
echo "<img src=\"stop.png\"/> The phone you are trying to use with mac $row[0] $row[1] is not a correct Mac\Type. <br> Please confirm the mac and type again.";
}

?>


I believe my syntax is correct as my if echo works fine. However, when no records are found I do not see my last else echo. Any input?

hi i am having a problem with a php script. i have 3 files index.php,  home.html.php, form.html.php.
home.html.php is working fine, if has a form that is submitting fine but when it submits i get a problem. maybe someone can help me out please i would be grateful as i am new. the is nothing wrong with anything else mysql database is running fine and there is no problem with connection.

index.php (you can ignore what is commented out)

<?php
 include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';
 //$_GET['id'] is categoryid
if (isset($_GET['id']))
{
   include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

   $id = mysqli_real_escape_string($link, $_GET['id']);
   $sql = "SELECT businessid FROM businesscategory WHERE
          categoryid LIKE '$id'";   
  $result = mysqli_query($link, $sql);
  if ($result !='')
  {
   $businessid = mysqli_fetch_array($result);
  $sql = "SELECT content FROM business WHERE
          id LIKE '$businessid'"; 
  $result = mysqli_query($link, $sql); 
  }
  if ($result !='') 
  {
  $content = mysqli_fetch_array($result);
  include 'form.html.php';
   exit();
  }
} 

/*
   // The basic SELECT statement
   $select = 'SELECT content';
   $from   = ' FROM business';
   $where  = ' WHERE TRUE';

   $id =  mysqli_real_escape_string($link, $_GET['id']);
   if ($category != '') // An owner is selected
   {
      $where .= " AND categoryid='$categoryid";
   }

   $categoryid =  mysqli_real_escape_string($link,
         $_GET['category']);
   if ($categoryid != '') // A category is selected
   {
      $from  .= ' INNER JOIN businesscategory ON id = business';
      $where .= " AND categoryid='$categoryid'";
   }

   $text = mysqli_real_escape_string($link, $_GET['text']);
   if ($text != '') // Some search text was specified
   {
      $where .= " AND content LIKE '%$text%'";
   }

   $result = mysqli_query($link, $select . $from . $where);
   if (!$result)
   {
      $error = 'Error fetching businesses.';
      include 'error.html.php';
      exit();
   }

   while ($row = mysqli_fetch_array($result))
   {
      $businesses[] = array('id' => $row['id'], 'text' => $row['content']);
   }

   include 'form.html.php';
   exit();
} 
 
  */
 
 
 
 
 
 
$result = mysqli_query($link, 'SELECT id, name FROM category ORDER BY name');
if (!$result)
{
   $error = 'Error fetching categories: ' . mysqli_error($link);
   include 'error.html.php';
   exit();
}

while ($row = mysqli_fetch_array($result))
{
   $categories[] = array('id' => $row['id'], 'text' => $row['name']);
}

include 'home.html.php';
?>

form.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .
      '/includes/helpers.inc.php'; ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=windows-1250">
  <meta name="generator" content="PSPad editor, www.pspad.com">
  <title>Business</title>
  </head>
  <body>
       it works
       <?php echo $content; ?>
  </body>
</html>

How do I get LIKE to check multiple values? It works if you type in a single word, but not multiple, which I want it to in order to get an accurate search result. Here is what I am trying to do:

$target = "siemens 6s" ;

$multis = explode(" ", $target) ;

$query = "SELECT parts_table.*, manufacturers.* FROM parts_table, manufacturers WHERE parts_table.ManufacturerID = manufacturers.ManufacturerID AND parts_table.PartNumber LIKE" ;

foreach($multis as $current) {

$queryext .= " '%$current%' OR" ;

} $queryext = substr($queryext, 0, -3) ; // Remove the last 4 charecters i.e. " AND".

$query .= $queryext ;

echo $query .= " OR manufacturers.ManufacturerID = parts_table.ManufacturerID AND manufacturers.ManufacturerName LIKE" . $queryext ;



include("dbconnect.php") ;

$result = mysql_query($query) ;

while($row = mysql_fetch_assoc($result)){

echo $row['ManufacturerName'] ;
echo $row['PartNumber'] ;

}