Databases
StackOverflow2013
Postsdbo
POMySQL update query not working
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL update query not working
    primarykey
    data
    text
    <p>I have a MySQL table which has the following columns</p> <pre><code>urlByCustomer table ---------------------------------------------- |customerID | TeamID | date | numUrlsConsumed| ---------------------------------------------- | | | | | ---------------------------------------------- urlmapping Column Type Null Default Comments MIME urlMappingID bigint(20) No customerID int(11) Yes 0 activityID int(11) Yes 0 contactID int(11) Yes 0 fullURL mediumtext Yes NULL lastModified timestamp No CURRENT_TIMESTAMP developerSandbox varchar(25) Yes </code></pre> <p>I've got this code that is being executed to update the table</p> <pre><code> $start = strtotime(date('Y-m-d 00:00:00')); $end = strtotime(date('Y-m-d 23:59:59')); $countAllThisGuysVals = "SELECT COUNT( DISTINCT`customerID`,`fullURL`) FROM `urlmapping` WHERE `urlMappingID` &gt;= $ORIGINAL_COPY AND `customerID` = $currentCustomerID"; $countTheVals= $conn-&gt;query($countAllThisGuysVals); $countedfinal =0; foreach ($countTheVals as $countRow) { $countedfinal = array_sum($countRow)/2; } $tableUpdateQuery = "UPDATE `urlByCustomer` SET `date` = NOW() WHERE `customerID`= $currentCustomerID AND UNIX_TIMESTAMP(`date`) BETWEEN '{$start}' and '{$end}'"; $conn-&gt;exec($tableUpdateQuery); $tableUpdateQuery = "UPDATE `urlByCustomer` SET `numUrlsConsumed` = $countedfinal WHERE `customerID`= $currentCustomerID AND UNIX_TIMESTAMP(`date`) BETWEEN '{$start}' and '{$end}'"; $conn-&gt;exec($tableUpdateQuery); echo "update path &lt;br&gt;"; $tableModified = true; $originalLPID++; </code></pre> <p>Variables are pretty much all declared, but the declarations are spread out, so I'm just posting this part to shorten it. The update query to the date column seems to be working, but the second update isn't. It worked 17 minutes ago though, so I'm confused since the only thing that changed between the next test was that I added some new values that should be causing it to update that column.</p> <p>Idk. I guess one possiblity could be the UNIX_TIMESTAMP. I'm running this in Parallels on a Mac, so I'm not sure what that translates to for timestamps.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSlayer0248
    1. USSlayer0248
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSo instead of working, it's doing what, exactly?
      singulars
      1. POMySQL update query not working
      1. USBrilliand
    2. COCould you give the structure for `urlmapping` please, and in your code, you refer to table `urlByCustomer` but in your post you call it `customerByUrl` - is this the same table?
      singulars
      1. POMySQL update query not working
      1. USFluffeh
    3. COHave you echoed your query and run directly in your database, like in phpmyadmin or something like that? To check the errors?
      singulars
      1. POMySQL update query not working
      1. USRohit Kumar Choudhary
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload