Jump to content

Gilbert

Members
  • Posts

    63
  • Joined

  • Last visited

  • Days Won

    1

Everything posted by Gilbert

  1. Hi Ingolme, I'm sorry I didn't get an alert in my email when you answered. I definitely have rows in the table that match the date - it looks just like the '2017-12-05' - and it is of type 'date' as I stated. The query returns the whole list with all dates when I run it without the WHERE clause. I was wondering if there was another syntax of 'workdate that I should be using. AHHHH! I think I just figured it out - I think I have the Joins in the wrong order because I'm trying to get the date which is in the 'Kiosks' table and I'm starting with 'Locations'. I'm going to try it and let you know.....Hooray! Hoorah! It's alive!!! I hope I helped someone else who might look at this. Thanx, Gil
  2. Hi – I’m having a lot of trouble with a where clause with a date in my sql query. It seems I have tried everything and read many articles & blogs, but I can’t get it working. I understand the data type is very important to match up and the format also. Anyway I’ll tell you what I’ve got and see if anyone can help me. I’m joining 3 tables in my query – everything works fine and I get the results I want UNTIL I add the where clause. Unless there is something I need to change because of the where clause, I shouldn’t need advice on that. Tables Fields Kiosks – kioskID, workDate, locationID, workerID, bills, coins - workDate is type Date Locations - locationID, storeName, branchName, locationAbbrev Workers - workerID, firstName, lastName, phoneNum $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID”; This part works fine and I get a long list showing the Date, Location, Worker, and Amount using PHP. BUT,(everybody’s got a big BUTT) when I tack on the Where clause – kapoof!! When I try different formats I get either 0 results or unable to process messages. Here are a couple of things I’ve tried – hopefully someone can spot the error in my ways. Thanks $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID WHERE Kiosks.workDate = '2017-12-05'"; Also tried without quotes like = 2017-12-05 $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID WHERE year(Kiosks.workDate) = 2017 AND month(Kiosks.workDate) = 12 AND day(Kiosks.workDate) = 5";
  3. I have used combo boxes for years in Access and the 'select' element on an html form is similar. All of the tutorials on w3 show how to fill the 'options' with literal values, but I would like to have the options be filled from a table in a database so the user can select one. I know this is probably a cross-topic item and I have an idea of how to go about it using javascript, php and sql, but I thought I'd ask if anyone knew of a procedure which is already created for this. In Access you just enter the row source for the combo box and the options are there - is there any kind of construct to do that for a 'select' element in html? Thanks
  4. I'm learning php & sql. I can do the programming and logic, but how to put it all together is where I get confused. I have copied, from the w3 tutorial, the php form validation complete, and it worked fine. I have also used the php/sql database tutorial to copy the 'insert into' code and it works fine with a Db I set up on the local host. Now I would like to save the info in the form to a database table, but I'm not sure where to insert the code to connect to the database. I tried just adding the code at the bottom of the 'Form Complete' code but it didn't seem to take - no error codes but didn't get saved to the table. If I should keep it as a separate file, how and where do I call that file with the parameters I want to insert into the database? Is there anything on the w3 site that deals with this 'real life' situation. The tutorials are very good, but (everybody's got a big butt) the examples all use literals and they don't relate to a problem like I've explained here. I mean, why else would you have somebody fill in a form except to save the info in a table for future reference?? Any help going forward would be greatly appreciated. I didn't post the code here to save space, so if you're not sure what code I'm talking about, it's at w3schools and under PHP tutorial goto PHP forms 'Form Complete' and just below that in the SQL section is the code for Inserting data into a mySQL database. If I need to, I will post it. Thanx, Gil
  5. I am learning a lot about html,css,js,php,sql etc, but it seems like I don't have a solid grasp on which language to use where; like should I use the DOM with JavaScript to read an XML file, or should I use PHP to get info from a SQL database. It seems like it jumps around from jQuery to Ajax to SQL all in the same example. Can anyone recommend a book or article to kind of tie it all together or does w3schools have more along these lines. Thank you any advice you can offer to help me out.
  6. Hey iwato, thanx for your quick response - I only had time to have a sandwich. I thought that 'XMLHttpRequest' was a reserved word - as a constructor or like. I will give it a whirl and let you know. How do I award a trophy. I asked another responder how to mark a post as answered and he said people just change the title of their request to 'solved'. Can you give me a heads up on that? thanks, Gil I have changed the variable names to separate the 2 functions. Still the same - I see what you were saying, but I figured one function would be done before the other took effect. Here's what I've got now. <!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>Service Club Collections</title> <style> .wholePage { position: relative; width: 1350px; height: 2000px; border: 2px solid red; margin:auto;} .scColl { position:absolute; top:0; left:0; width:1050px; height:2000px; border:2px solid red;} .scTot { position: absolute; top: 0; right: 0; width: 250px; height: 2000px; border: 2px solid red;} #collections { position:absolute; top:0; left:0;} #totals { position:absolute; top:0; right:0;} table, th, td { border: 1px solid red; border-collapse: collapse;} th, td { padding: 7px 10px;} th { font-size:14px; color: firebrick;} td { font-size:12px; color: darkolivegreen; text-align: center;} </style> </head> <body> <a href="ringerinfo.html" type="button" style="margin:auto">Return</a> <div class="wholePage"> <div class="scColl"> <table id="collections"></table> </div> <div class="scTot"> <table id="totals"></table> </div> </div> <script> (function loadXMLCollDoc() { var xmlhttpcoll = new XMLHttpRequest(); xmlhttpcoll.onreadystatechange = function () { if (this.readyState == 4 && this.status == 200) { myFunction(this); } }; xmlhttpcoll.open("GET", "datafiles/servClubColl.xml", true); xmlhttpcoll.send(); })(); function myFunction(xmlcoll) { var i; var xmlCollDoc = xmlcoll.responseXML; var table ="<tr><th>Service Club</th><th>Ring Date</th><th>Location</th><th>Start Time</th><th>End Time</th><th>Total Bills</th><th>Total Coins</th><th>Total Checks</th><th>Total Kettle</th></tr>"; var x = xmlCollDoc.getElementsByTagName("scCollections"); for (i = 0; i <x.length; i++) { table += "<tr><td>" + x[i].getElementsByTagName("RingerName")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("RingDate")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Location")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("SchedStart")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("SchedEnd")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Bills")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Coins")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Checks")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("AmountCollected")[0].childNodes[0].nodeValue + "</td></tr>"; } document.getElementById("collections").innerHTML = table; } (function loadXMLTotDoc() { var xmlhttptot = new XMLHttpRequest(); xmlhttptot.onreadystatechange = function () { if (this.readyState == 4 && this.status == 200) { myFunction(this); } }; xmlhttptot.open("GET", "datafiles/scTotals.xml", true); xmlhttptot.send(); })(); function myFunction(xmltot) { var i; var xmlTotDoc = xmltot.responseXML; var table = "<tr><th>Service Club</th><th>Seasonal Total</th></tr>"; var x = xmlTotDoc.getElementsByTagName("Totals"); for (i = 0; i < x.length; i++) { table += "<tr><td>" + x[i].getElementsByTagName("ServClub")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("TotalAmount")[0].childNodes[0].nodeValue + "</td></tr>"; } document.getElementById("totals").innerHTML = table; } </script> </body> </html>
  7. Hi, I've made an html page that invokes a function automatically ( using the parentheses b/4 & after ) to make a table from an XML file. It worked great with a little help from you guys. I then made another one to read a different XML file and that worked great. I want to put both tables on the same page so I made a couple of 'divs' and positioned each in a column formation. That part seems to work well, but I am calling the 2 functions to make the tables consecutively and it doesn't work as I wanted. When I open this file in a browser, as it is here, I get only the second table in the second column correctly; and if I comment out the second table, I get only the first table in the first column correctly. I've included the code below; I hope someone can tell me how to do this. Also just a sample of the 2 XMLs I'm trying to display. Do I need a wrapper function with the others invoked from it, or something like that. Thanks <!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>Service Club Collections</title> <style> .wholePage { position: relative; width: 1350px; height: 2000px; border: 2px solid red; margin:auto;} .scColl { position:absolute; top:0; left:0; width:1050px; height:2000px; border:2px solid red;} .scTot { position: absolute; top: 0; right: 0; width: 250px; height: 2000px; border: 2px solid red;} #collections { position:absolute; top:0; left:0;} #totals { position:absolute; top:0; right:0;} table, th, td { border: 1px solid red; border-collapse: collapse;} th, td { padding: 7px 10px;} th { font-size:14px; color: firebrick;} td { font-size:12px; color: darkolivegreen; text-align: center;} </style> </head> <body> <a href="ringerinfo.html" type="button">Return</a> <div class="wholePage"> <div class="scColl"> <table id="collections"></table> </div> <div class="scTot"> <table id="totals"></table> </div> </div> <script> (function loadXMLCollDoc() { var xmlhttp = new XMLHttpRequest(); xmlhttp.onreadystatechange = function () { if (this.readyState == 4 && this.status == 200) { myFunction(this); } }; xmlhttp.open("GET", "datafiles/servClubColl.xml", true); xmlhttp.send(); })(); function myFunction(xmlcoll) { var i; var xmlDoc = xmlcoll.responseXML; var table ="<tr><th>Service Club</th><th>Ring Date</th><th>Location</th><th>Start Time</th><th>End Time</th><th>Total Bills</th><th>Total Coins</th><th>Total Checks</th><th>Total Kettle</th></tr>"; var x = xmlDoc.getElementsByTagName("scCollections"); for (i = 0; i <x.length; i++) { table += "<tr><td>" + x[i].getElementsByTagName("RingerName")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("RingDate")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Location")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("SchedStart")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("SchedEnd")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Bills")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Coins")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("Checks")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("AmountCollected")[0].childNodes[0].nodeValue + "</td></tr>"; } document.getElementById("collections").innerHTML = table; } (function loadXMLTotDoc() { var xmlhttp = new XMLHttpRequest(); xmlhttp.onreadystatechange = function () { if (this.readyState == 4 && this.status == 200) { myFunction(this); } }; xmlhttp.open("GET", "datafiles/scTotals.xml", true); xmlhttp.send(); })(); function myFunction(xmltot) { var i; var xmlDoc = xmltot.responseXML; var table = "<tr><th>Service Club</th><th>Seasonal Total</th></tr>"; var x = xmlDoc.getElementsByTagName("Totals"); for (i = 0; i < x.length; i++) { table += "<tr><td>" + x[i].getElementsByTagName("ServClub")[0].childNodes[0].nodeValue + "</td><td>" + x[i].getElementsByTagName("TotalAmount")[0].childNodes[0].nodeValue + "</td></tr>"; } document.getElementById("totals").innerHTML = table; } </script> </body> </html> Here is just a sample of the XML data files *********************************** <?xml version='1.0' encoding='utf-8'?> <dataroot> <scCollections> <RingerName>1st National bank scotia</RingerName> <RingDate>Dec 12 - Mon</RingDate> <Location>Price Chopper Niskayuna Left Door</Location> <SchedStart>9:00 am</SchedStart> <SchedEnd>9:00 pm</SchedEnd> <Bills>$ 243</Bills> <Coins>$ 27</Coins> <Checks>$ 00</Checks> <AmountCollected>$ 270</AmountCollected> </scCollections> <scCollections> <RingerName>Advisory Board</RingerName> <RingDate>Dec 9 - Fri</RingDate> <Location>Co-Op Nott St Niskayuna</Location> <SchedStart>9:00 am</SchedStart> <SchedEnd>1:00 pm</SchedEnd> <Bills>$ 131</Bills> <Coins>$ 15</Coins> <Checks>$ 00</Checks> <AmountCollected>$ 146</AmountCollected> </scCollections> <scCollections> <RingerName>Beukendall Eastern Star</RingerName> <RingDate>Dec 7 - Wed</RingDate> <Location>Price Chopper Glenville Main Dr</Location> <SchedStart>9:00 am</SchedStart> <SchedEnd>7:00 pm</SchedEnd> <Bills>$ 283</Bills> <Coins>$ 64</Coins> <Checks>$ 100</Checks> <AmountCollected>$ 447</AmountCollected> </scCollections> <scCollections> <RingerName>Beukendall Eastern Star</RingerName> <RingDate>Dec 8 - Thu</RingDate> <Location>Price Chopper Glenville Main Dr</Location> <SchedStart>9:00 am</SchedStart> <SchedEnd>7:00 pm</SchedEnd> <Bills>$ 415</Bills> <Coins>$ 38</Coins> <Checks>$ 00</Checks> <AmountCollected>$ 453</AmountCollected> </scCollections> </dataroot> AND ********************************* <?xml version='1.0' encoding='utf-8'?> <dataroot> <Totals> <ServClub>1st National bank scotia</ServClub> <TotalAmount>$ 270</TotalAmount> </Totals> <Totals> <ServClub>Advisory Board</ServClub> <TotalAmount>$ 146</TotalAmount> </Totals> <Totals> <ServClub>Beukendall Eastern Star</ServClub> <TotalAmount>$ 900</TotalAmount> </Totals> <Totals> <ServClub>Corps Band</ServClub> <TotalAmount>$ 190</TotalAmount> </Totals> <Totals> <ServClub>General Electric Volunteers</ServClub> <TotalAmount>$ 2505</TotalAmount> </Totals> </dataroot>
  8. Thank you so much for your reply. Sorry it took so long to get back to you - it seems I don't get an email when someone replies. Both of your answers worked out perfectly and I can now do the XML with no problems. Just for future reference, I don't know why the file I copied came out like triple spaced. I copied and pasted - is there a better way? And I don't see where to mark this question as answered. In other forums they usually have a button right here that says 'answered'. Thanx again dsonesuk!
  9. Hi, I've read the json tutorial on w3 website, and just have one question which makes a huge difference in the way I create the layout of my json file. All the examples they gave had a very short json file to read, but how does a json file look when you're trying to read many records. Does each httpRequest read the whole file from one first '{' to the last '}' and that's it. Or, if there are several, {'s will it return each one as an object. It was unclear to me from the tutorials. Specifically, If I had a database with 10 records and 5 fields in each, would I do this: { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" }..... etc for 10 times, or would I do this with a name for my 'database' and make an array of the records: { databaseName [ { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } { "name1" : "value1", "name2" : "value2", "name3" : "value3", "name4" : "value4", "name5" : "value5" } .......etc to 10 records as an array ] } I think I'm understanding it as the second example - is this correct or am I missing something? Would it be all right to put 500 records in an array? I don't know how often they update the w3 website, but maybe they could emphasize this better. I know they went into creating the json file from a database using php and some other methods, but I want to create it myself and then upload the file to the server. One other question is that in the tutorial it said a json file has a json file type, but then they used a file in the example with a .txt type. I know json files are text files; but can you use .txt on your json file?
  10. I copied and pasted a file from the w3 tutorial XML/AJAX application but changed just a couple of spots so it could read my own XML data file. It doesn't work - what am I missing, please. When I open the console in either google or IE it says xmlDoc is null or undefined - somehow I'm not 'reading' the file or something? can anybody explain it more. I've read over the toturials a couple of times and I think it should be working. I have also cleaned out the cach in the browser by hitting cntrl F5, which I learned from someone along the way. <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>S.C. Information</title> <style> table, th, td { border: 1px solid green; border-collapse:collapse; } th, td { padding: 5px; } </style> </head> <body> <button type="button" onclick="loadXMLDoc()">Get my S.C. Report</button> <br><br> <table id="demo"></table> <script> function loadXMLDoc() { var xmlhttp = new XMLHttpRequest(); xmlhttp.onreadystatechange = function() { if (this.readyState == 4 && this.status == 200) { myFunction(this); } }; xmlhttp.open("GET", "scTotals.xml", true); xmlhttp.send(); } function myFunction(xml) { var i; var xmlDoc = xml.responseXML; var table="<tr><th>Service Club</th><th>Seasonal Total</th></tr>"; var x = xmlDoc.getElementsByTagName("Totals"); for (i = 0; i <x.length; i++) { table += "<tr><td>" + x.getElementsByTagName("ServClub")[0].childNodes[0].nodeValue + "</td><td>" + x.getElementsByTagName("TotalAmount")[0].childNodes[0].nodeValue + "</td></tr>"; } document.getElementById("demo").innerHTML = table; } </script> </body> </html> Data File 'scTotals.xml' in same website folder. <?xml version='1.0' encoding='utf-8'?> <dataroot> <Totals> <ServClub>Beukendall Eastern Star</ServClub> <TotalAmount>$ 447</TotalAmount> </Totals> <Totals> <ServClub>Key Club</ServClub> <TotalAmount>$ 363</TotalAmount> </Totals> <Totals> <ServClub>S.I. Group</ServClub> <TotalAmount>$ 201</TotalAmount> </Totals> <Totals> <ServClub>Sarah Bilofsky & Friends</ServClub> <TotalAmount>$ 182</TotalAmount> </Totals> <Totals> <ServClub>Schenectady ARC</ServClub> <TotalAmount>$ 1530</TotalAmount> </Totals> </dataroot> Any help would be appreciated. Gil
  11. Thank you very much justsomeguy - that really helped 'splain it. I'll mark this as answered if I can find where to do it.?
  12. Hey, justsomeguy - thanx for your response. I'm am a little hazy ( actually a lot hazy ) on the whole server thing. I thought I read someplace that if your data file (xml) was in the same folder as your html file that it would work ok. I've tried putting the whole path for my xml file, like c:\ learningwebdesign\myhtml etc, etc but it still doesn't work. Do I need to download a server just to experiment with a few things and which ones would you recommend. It seems like I've read a thousand pages about servers, but they seem to talk to the experienced developer instead of the beginner and I am still wondering if I 'need' a server on my computer before I'm ready to publish to a hosted website. Can you recommend some server software that will accomplish what I need here, which is just to see if my code works. Actually the 'code' works - I just can't get to the data it seems. I know you may be saying that my syntax might be wrong or something like that, but I copy & pasted a couple of programs and they don't work on my set up either. I hope I've explained what it is I need - Thanks for any help..... Gil
  13. Hi all, I'm just starting to get into website design, tho' I've been a programmer for many years. I've got a site going pretty well, but I really want to update the information on the site every day using tables. I am studying the AJAX/XML method and I think I must be missing something. After using the 'display CD collection' as a model, I couldn't get it to work. So then I tried copying & pasting the actual files from the example ( they list the xml file for your reference ) into a folder and Opened it with a google browser. I can get the button that says 'create my CD collection', but nothing happens when I click it. What am I missing; is there another article that might explain this in more detail and say exactly what's going on? Thanks
×
×
  • Create New...