manhattes

Jacques1 Advanced Member 1,770 posts Posted Yesterday, 10:23 PM INSERT IGNORE with Link being a primary or unique key. Like This Best Answer Quote MultiQuote Thanks. Changed the row value to primary.

I fixed it by just keeping my code and assigning the row as a primary. thanks!

Something like IF Link NOT EXIST INSERT....

What do you mean do a query for link? How can I do the query for an incoming link before its in the table?

sorry that issue was solved and it led to this question. wasn't sure of protocol.

The script pulls in the last 1000 results of a feed and sometimes if it updates within the 1000 results there are duplicates. The column that has the link is the best way to identify the row as a duplicate so if I already have it then I dont need to save it....

I am reading an RSS feed and sometimes I pull in duplicates of data. $citable= "INSERT INTO TableName (Company, Form, Date, Link) VALUES ('" . $FnumI ."',' " . $Form ."','" . $msqldate ."','" . $Flink ."') ON DUPLICATE KEY UPDATE Company='" . $FnumI ."', Form='" . $Form ."', Date='" . $msqldate ."', Link='" . $Flink ."'"; Is there a way I can only record the data if Link does not exist?

Thanks it is slow but worked. my script pulls in an RSS feed and sometimes pulls in data that was already pulled in. $citable= "INSERT INTO TableName (Company, Form, Date, Link) VALUES ('" . $FnumI ."',' " . $Form ."','" . $msqldate ."','" . $Flink ."') ON DUPLICATE KEY UPDATE Company='" . $FnumI ."', Form='" . $Form ."', Date='" . $msqldate ."', Link='" . $Flink ."'"; Is there a way I can only record the data if Link does not exist? Yes there is always an id.

I am trying to delete all the rows that have a duplicate URL in my table. I tried this code: but it is giving me an error: You can't specify target table 'TableName' for update in FROM clause DELETE FROM TableName WHERE ID NOT IN (SELECT MAX(ID) FROM TableName GROUP BY Link HAVING MAX(ID) IS NOT NULL)

I am trying to read an RSS feed and the way my code is written, it is only repeating the first result. the xml looks like this: <?xml version="1.0" encoding="ISO-8859-1" ?> <feed xmlns="http://www.w3.org/2005/Atom"> <title>Latest Filings - Thu, 18 Feb 2016 14:52:36 EST</title> <link rel="alternate" href="/cgi-bin/browse-edgar?action=getcurrent"/> <link rel="self" href="/cgi-bin/browse-edgar?action=getcurrent"/> <id>http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent</id> <author><name>Webmaster</name><email>[email protected]</email></author> <updated>2016-02-18T14:52:36-05:00</updated> <entry> <title>4 - YABUKI JEFFERY W (0001207371) (Reporting)</title> <link rel="alternate" type="text/html" href="http://www.sec.gov/Archives/edgar/data/1207371/000120919116099734/0001209191-16-099734-index.htm"/> <summary type="html"> <b>Filed:</b> 2016-02-18 <b>AccNo:</b> 0001209191-16-099734 <b>Size:</b> 13 KB </summary> <updated>2016-02-18T14:51:18-05:00</updated> <category scheme="http://www.sec.gov/" label="form type" term="4"/> <id>urn:tag:sec.gov,2008:accession-number=0001209191-16-099734</id> </entry> <entry> <title>4 - FISERV INC (0000798354) (Issuer)</title> <link rel="alternate" type="text/html" href="http://www.sec.gov/Archives/edgar/data/798354/000120919116099734/0001209191-16-099734-index.htm"/> <summary type="html"> <b>Filed:</b> 2016-02-18 <b>AccNo:</b> 0001209191-16-099734 <b>Size:</b> 13 KB </summary> <updated>2016-02-18T14:51:18-05:00</updated> <category scheme="http://www.sec.gov/" label="form type" term="4"/> <id>urn:tag:sec.gov,2008:accession-number=0001209191-16-099734</id> and my code is like this: $context = stream_context_create(array('http' => array('header' => 'Accept: application/xml'))); $url = 'http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent&CIK=&type=4&company=&dateb=&owner=include&start=100&count=20&output=atom'; $xml = file_get_contents($url, false,$context); $xml = simplexml_load_string($xml); print_r($xml); foreach ($xml->entry as $entry){ $FnumI=$xml->entry->title; //$FnumC=$xml->entry->title; $Fdate=$xml->entry->updated; $Flink=$xml->entry->link->attributes()->href; $Form=$xml->entry->category->attributes()->term; echo "<tr>"; echo "<td><p>".$FnumI."</p></td>"; //echo "<td><p>".$FnumC."</p></td>"; echo "<td><p>".$Fdate."</p></td>"; echo "<td><p><a href='".$Flink."'> ". $Flink."</p></td>"; echo "<td><p>".$Form."</p></td>"; echo "</tr>"; }

I have 2 tables Table1 is names,addresses,phone Table2 has other info that i want including the names In table2 the names are in a cell grouped together like this: James Madison|University of Iowa|Jeff Hall What is the best way to join the table since there is no exact match? Is it a str_search or something?

I imported a table into mysql and the dates within the table are formatted "16-Feb" where the 16 is the Year not the day. Day has no value. I tried to convert it in excel first but it kept switching the day with the year. Which command should i use to format the dates properly within SQL?

I can no longer open the table after I ran this query and it appeared to run correctly. :-( $query2 = "UPDATE ". $symbol ." b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Date SET a.CalDatePrice = b.Close";

Little background on the DB Table1 is CleanedCalendar Table2 is $symbol (there is a symbol table for each symbol which has 1 entry per day making Date a unique column) I want to set the column CalDatePrice = to the price in the symbol table on the matched date.

Still doesnt work. $symbol is only letters with no spaces

That gives me an internal server error. Looking at the syntax page, it would appear it should be formatted like this: $query2 = "UPDATE CleanedCalendar, ". $symbol ." SET CleanedCalendar.CalDatePrice = ". $symbol .".Close WHERE CleanedCalendar.BuyDate = ". $symbol .".Date"; However this also gives an internal Server error

I am trying to set a column equal to the column in another table where the date matches. I seem to have a problem in my query and I have tried multiple methods. Can someone tell me where the mistake is? $query2 = "UPDATE a SET a.CalDatePrice = b.Close FROM ". $symbol ." b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Date"; 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 'FROM CLBS b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Dat' at line 3

I am having trouble getting the element values i want from the following xml <entry> <category label="form type" scheme="http://www.sec.gov/" term="8-K" /> <content type="text/xml"> <accession-nunber>0001269026-15-000064</accession-nunber> <act>34</act> <file-number>001-33624</file-number> <file-number-href>http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&filenum=001-33624&owner=exclude&count=40</file-number-href> <filing-date>2015-11-10</filing-date> <filing-href>http://www.sec.gov/Archives/edgar/data/1269026/000126902615000064/0001269026-15-000064-index.htm</filing-href> <filing-type>8-K</filing-type> <film-number>151219153</film-number> <form-name>Current report</form-name> <items-desc>items 8.01 and 9.01</items-desc> <size>36 KB</size> </content> <id>urn:tag:sec.gov,2008:accession-number=0001269026-15-000064</id> <link href="http://www.sec.gov/Archives/edgar/data/1269026/000126902615000064/0001269026-15-000064-index.htm" rel="alternate" type="text/html" /> <summary type="html"> <b>Filed:</b> 2015-11-10 <b>AccNo:</b> 0001269026-15-000064 <b>Size:</b> 36 KB<br>Item 8.01: Other Events<br>Item 9.01: Financial Statements and Exhibits</summary> <title>8-K - Current report</title> <updated>2015-11-10T16:17:52-05:00</updated> </entry> My php code is as follows: $xml = file_get_contents($url, false, $context); $xml = simplexml_load_string($xml); foreach ($xml->entry as $entry){ $Fnum=$entry->{'filing-type'}; $Fdate=$entry->{'filing-date'}; $Flink=$entry->{'filing-href'}; $Form=$entry->{'form-name'}; echo "<li>".$Fnum.$Fdate.$Flink.$Form."</li>"; }It just doesnt load anything. If I make it: foreach ($xml->entry[0] as $entry){ Then it only returns that entry. How can I have it return all of the entries?

Thanks that was so much easier then the others!

SELECT `BuyDate` FROM `Calendar` DATE_ADD( `BuyDate`, INTERVAL ( 9 - IF ( DAYOFWEEK(`BuyDate`) = 1, 8, DAYOFWEEK(`BuyDate`) ) ) DAY ) AS NEXTMONDAY; #1064 - 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 'DATE_ADD( `BuyDate`, INTERVAL ( 9 - IF ( DAYOFWEEK(`BuyDate`) ' at line 2

The dates are used for my own purpose and having them fall on weekdays don't help since I dont work on Sat. and Sun. ussually. haha I was scolded once on here for having dates stored in VARCHAR. They are stored as a date and formatted Y-m-d or ex. 2015-10-24

no I have been storing new dates as dates as instructed. :-) I want to change the dates to either Friday or Monday. Running 2 queries is fine. I dont understand your select. when i change the dates to the column date it doesnt work. Syntax error on Dateadd

no I have been storing new dates as dates as instructed. :-)

So my date is stored as Y-m-d Update Table.date = date + (1 day) WHERE DAYOFWEEK(date)=1 and Update Table.date = date - (1 day) WHERE DAYOFWEEK(date)=7 ?

Is there an easy way to change dates in a column so that if the date falls on a Saturday or Sunday it changes to the closest business day?