Jump to content
GX1705

PHP fetching mysqli db, not resulting as expected/tried.

Recommended Posts

So glad I found this forumn!  Greets to everyone.
I have been (re)learning (or trying to) some PHP code.

i have been working on this script for a semi-simple facebook bot.
to send a 'verse' (or chapter "quote"-(that i already have in my SQL DB ready and right.) from a 'book'.  The last part is getting it to work with this FB "bot", it works on my PAGE (i can manipulate the $input variable,  (acting like a person inputting something to the Bot, i just change the $string ($messageText), when i copy it
over to the actual 'bot.php' script, everything works as required, simple 'auto-replies' to 'given' phrases, no replies to just general 'chat'. etc..
when i use the necessary line (which works great in the test/localhost) it all works Except that one call (which is the whole point of the bot mostly).
when i use the 'secondary' line (simple, very similar, but Not accessing the SQLi) it works ... i'm stumped, they're practically identical (in logic/theroy).
here is the part i'm trying to defeat at this moment.  Thanks for your attention, and suggestions.. :)
 

[...]
     if ($msgat == "@"){
    //$answer = "This works.";           // <-- This Does Work. a simple $string.. live, within the use of the bot.
    $answer= " " . $row["verse"];       // <-- This does Not work, but it does 'locally' ??why not? :)
}
[...]

pastebin code is here. (https://pastebin.com/DJyAJZVb)
it all checks out, as far as i can make it, i have ran it through PHPcheckers, php -l, and loaded it into various editors to help determine missing "brackets, etc".
it works except for that one area.  here is what i think matters for knowing, so far. (or you can check out the pastebin link, thanks!

[...]

$mysqli = "SELECT verse FROM ".$datatable." WHERE book LIKE '%".$message."'";
 
$result = $conn->query($mysqli);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) ;
    //$conn->close();  
}
{
 
if ($msgat == "@"){
  //$answer = "This works.";          // <-- This Does Work. a simple $string.. live, within the use of the bot.
    $answer= " " . $row["verse"];    // <-- This does Not work, but it does 'locally' ??why not? :)
}
//echo $answer; // <-- used in localhost test script it works and echos either or of the above $answer showing correctly.
           
else if($msgat[0] != "@"){

 $answer = $answer;}
[...]

-------------------------------------------------------------------------
Any suggestions,help, or direction would be greatly appreciated, i have spent probably.. well, too long on this.. lol
Cheers, and thanks for having this forum.

GX1705

 

 

Share this post


Link to post
Share on other sites

Take a look at the code where you fetch the verse from the database.

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) ;
    //$conn->close();  
}

Here's the thing: $row is only useful inside the body of the while loop, so... where is the body of the while loop?

Share this post


Link to post
Share on other sites

is that a trick question, lol ;)  ok so its basically stick inside that?  while() function?  i am pretty new to this sql anyways.
i appreciate your teaching/not just sharing 'the answer', the thing that gets me, is it DOES echo the correct result.
etc. but i think you are aware of that.. ok, so, my answer, most likely wrong is it is residing in the IF statement, "stuck".
?  i will be diligent and look also for proper routine, it is straight from W3Schools though, here.

my quote for you, .."In Teaching, Two people learn." :) Thanks!
 

Share this post


Link to post
Share on other sites

Well, I found some { } brackets must have been out of place, i'm sure that's nothing new, especially to a newbie like me. :)

 

The verse's Are now getting posted correctly to the Bot.
I have to fine tune it because the other repsonces now are not..
either or an if, something can go wrong, it will.. ;) hehe

I am on the correct path now, THANK YOU!

Any more tutoring/advise.. is *always* Appreciated..
I will let the thread know as soon as I complete my chase and hunt down. of the rouge bracket. :/
:)  Cheers.

Share this post


Link to post
Share on other sites

ok, so i thought I would try and expand on my code, now that it's working.. ha. ;)

if someone would like to just tell me if I am simply "missing/overlooking" a .period. or {bracket} or the like?
it is one of those things were "googling" doesn't explain it (in terms that I can apply anyways, without making Guesses. 😕

------------------------------------------------------------------------------------------------------------

[...]

list($message1, $message2) = explode("-", "$messageText", 2);
$message3 = substr($message1,1);
$message4 = substr($message2,0);

$mysqli = "SELECT verse FROM ".datatable." WHERE book BETWEEN '%".message3." AND ".message4." ' ";
    
$result = $conn->query($mysqli);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc())

   {$answer= " " . $row[0]["verse"].$row[1]["verse"];

}

   }

[...]

-------------------------------------------      

What I Think, is that i am not 'asking' with that last line in the proper format..
$answer= " " . $row[0]["verse"].$row[1]["verse"];

i get very confused with all the small things, .periods. [brackets] etc.
i definitely Have looked and tried various approaches from W3Schools..

ie;

BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

so THAT leads me to why i am concerned it is in that $answer= array.. somewhere/how.

Thanks for Any help. Sorry if it is tedious, believe me, i wish it wasn't so difficult for me. ;)
 
Cheers,
GX1705

Also, if anyone has any suggestions on using another way to 'check' php code, beit a specific (free) Editor? or Site..
I am happy to oblige. Thanks again.
Currently I am using bluefish, Aptana, and Notepad++
 

Share this post


Link to post
Share on other sites

$row is only going to be a single row from the result. That means the syntax to get something in it would be $row["name of column"] without any [0]s or [1]s.

I'm not sure why you seem to be trying to get two verses. What is some of the data you're dealing with and what are you trying to get your script to come up with?

Also, when you post code, it helps if you use the <> Code button to add it to your post. You can skip the [...]s that way too.

Also also,  Notepad++ is a good editor and many people use it so that's fine, but it doesn't seem to do syntax checking without some setup. Google is your friend. If you want a recommendation of a whole different editor then I use VS Code with the Intelephense plugin.

Share this post


Link to post
Share on other sites

Sorry, i should have included more 'precise' information.. i wasnt sure if that would help.
in essence, i am searching a DB that has a book name with chapters and a verse, like a Bible.
so, book would be "Gen1:1" verse would be that verse "something some more",  and so-forth "Gen1:2" and a "verse".

it works previously when i only fetch One Row. "Gen1:1" for example.
as you previously helped me with, so thank you.!

i am now looking at expanding the 'search/fetch' for more than one chapter/verse.  within the same book.
so what works, is previous, i search for
Gen1:1 I get just_that verse, but i would like to be able to retrieve Through multiple.
Gen1:1-Gen1:3 for example, would return those Three Verses
.  which I have all in separate rows in my DB.

 

DB table:
            column:     column:
          __________________
          |  book          verse

row1 |  Gen1:1       some words
row1 |  Gen1:2       some more words

----------------------------------------------------
I have been able to 'do the work-up' to splitting the search string into those two parts,
ie; $messageText = Gen1:1-Gen1:3, is become $message3, and $message4 (each as a 'string' without the "-" hyphen)

that's were I am Trying to get it to work. 

so i can search more than one.. the searches should all be consecutive, forward, and most likely within the same pre-fix (book.)

Thanks for your help, and i have noted the better way to implement 'code' here. and thanks for the advise on editors, yes, Notepad++
is nice, but exactly why I use Bluefish and Aptana, it has that 'syntax' error checking, it does help a lot, can't do it for you though. :)
Thanks again.

Sorry if this is too much, i am trying hard.. some things 'on the internet' really are getting either 'outdated' depreciations, and just general 'interests', :)
I'm old school, sorta.. so a lot of "Google" algorithms seem to put the newer (technology) search info on top.  anyways, thanks again. :)

I just noticed you have a file upload, heck, i would have sent you a pic if needed.  if that would help. 
(of the database tables, or whatever). 

GX

 

Share this post


Link to post
Share on other sites

If you want to support ranges then you're going to have to normalize your data some. That means not storing strings like "Gen1:1" that you have to parse, and instead storing the book name, chapter number, and verse number separately.

book | chapter | verse | text
-----+---------+-------+-----
Gen  |       1 |     1 | some words
Gen  |       1 |     2 | some more words

There is more you could do but I think this a big enough change for the time being. With this in place, searching should be much more straightforward.

To parse a string like "Gen1:1" (one book, chapter, and verse) you can use

if (preg_match('/(\w+[a-zA-Z])(\d+):(\d+)/', $string, $match)) {
	list(, $book, $chapter, $verse) = $match;
} else {
	// invalid
}

To parse a range like "Gen1:1-Gen1:3" (must be the same book) or "Gen1:1-1:3" (same chapter) or "Gen1:1-3" (just the verse) you can use

if (preg_match('/(\w+[a-zA-Z])(\d+):(\d+)-(?:\1?(\d+):)?(\d+)/', $string, $match)) {
	list(, $book, $chapter1, $verse1, $chapter2, $verse2) = $match;
	if (!$chapter2) {
		$chapter2 = $chapter1;
	}
} else {
	// invalid
}

But to search on a range you'll have to be a bit creative. Give it a shot and see what happens. If you think you have it working, post what you came up with.

Edited by requinix
mistake in code

Share this post


Link to post
Share on other sites

Perfect.. that's great.. and also just what I was 'afraid' of.. ;) lol
I'll be working on that, thank you.
 

Share this post


Link to post
Share on other sites

just a question, for my clarity in understanding;  when you say,
"syntax to get something in it would be $row["name of column"] without any [0]s or [1]s."

The [0] and [1]s were there because  I read (on SO/stackoverflow) that is how it will determine the first and second $row to read.  given the example of $row["nameofcolumn"] yes, that does work when just a single search, such as previously / originally posted Here. :)

I was trying, ha, to find the means to retrieving the two columns. (then i would have to expand that into 'however many' are requested. such as more than just 2. etc.  anyways, just thought I would mention that.
logically, it reads well.. but it seems as though it needs more than just logic. :) lol  creativity indeed. :)

Thanks for all your help.. i will have to look more into your 'expressions' on just how to do this..
btw- this is just a project, so i am having a good learning experience

oh and btw#2, here is the entire page i am working on,  i cleaned it up, and added proper //comments to help anyone that wants to try and understand it.. :)    all for fun. :)  (headaches are a bonus ;)
https://pastebin.com/x8BjYHQX

Edited by GX1705
Added entire script. pastebin.

Share this post


Link to post
Share on other sites
14 minutes ago, GX1705 said:

The [0] and [1]s were there because  I read (on SO/stackoverflow) that is how it will determine the first and second $row to read.  given the example of $row["nameofcolumn"] yes, that does work when just a single search, such as previously / originally posted Here. :)

That would be if you fetched all the rows at once. You

while($row = $result->fetch_assoc())

are only getting one at a time. Which is fine. Normal, even.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.